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

Question

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?

Answer 1

Hi @Lena Siskin ,

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:

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

Answer 2

Hallo @Miguel Caballero Sierra

I wanna ask something about this topic.
If I have 3 column that will be value column (example : Action time, Units, and Sales).
How to create that?
I already try, but it still not success.
Please, help me. Thank you