question

LenaSiskin-9341 avatar image
0 Votes"
LenaSiskin-9341 asked LenaSiskin-9341 commented

Excel Power Query, Use value in column as headers, Use value in other column as value

I have a table with 4 columns:
1. ID (The ID is not unique in this column)
2. Start Time
3. Action (max 8 different values possible, this can change in the future...)
4. Action Time

For each ID I need to calculate the differences between the Action Time of the different actions
In order to do so I need a different structure in my report:
1. ID (The ID should be unique here)
2. Start Time
3. For every separate Action I need a separate column
-> The header should be the name of the Action
-> The value in the column should be the DateTime of the Action
How can I do this with Power Query?126362-report-layout.png


power-query-not-supported
report-layout.png (56.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MiguelCaballeroSierra-6578 avatar image
0 Votes"
MiguelCaballeroSierra-6578 answered LenaSiskin-9341 commented

Hi @LenaSiskin-9341 ,


In the Power Query editor do this:

Use the operation (command): Pivot column, wich is under the Transform Tab.

In the window that appers make sure that the: Action Column is your Pivot Column, Action_Time your Value Column and in the advanced options, choose: No Aggregate

This is the result:

126386-lenasiskin-9341.png



The M code:


let
#"Yout Previous Step Name" = "Some Code ...",

 #"Pivot Column" = 
 Table.Pivot ( 
     #"Yout Previous Step Name", 
     List.Distinct ( 
         #"Yout Previous Step Name"[Action]
     ), 
     "Action", 
     "Action_Time"
 ),

 #"ReorderColumn" = 
 Table.ReorderColumns ( 
     #"Pivot Column",
     {"ID", "Start_Time", "Autoclose", "Autoreject", "Intake", "Confirm", "Close"}
 )

in
#"ReorderColumn"

I reorde the columns to see the result as your image

— Miguel










lenasiskin-9341.png (59.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks very much for your clear explanation! Muchas gracias !
It worked perfectly

0 Votes 0 ·