Tranpose rows to columns

[eggyweggs]

I have a table which shows actions taken by a user ordered by the time executed:

Date	ID	Steps	Action
14/04/2022	112232	Step 1	register
14/04/2022	112232	Step 2	register confirmation
14/04/2022	112232	Step 3	first login
14/04/2022	112232	Step 4	login
14/04/2022	112233	Step 1	register
14/04/2022	112233	Step 2	register confirmation
14/04/2022	112233	Step 3	first login
14/04/2022	112233	Step 4	login
14/04/2022	112233	Step 5	login fail

I want to tranpose the 2 last columns 'Steps' and 'Action' so that it looks like this:

Date	ID	Step 1	Step 2	Step 3	Step 4	Step 5
14/04/2022	112232	register	register confirmation	first login	login	register
14/04/2022	112233	register	register confirmation	first login	login	login fail

I've tried tranposing and grouping but I can't quite get it right, any help would be appreciated.

Thanks

[Vijay_A_Verma · Accepted solution]

Select column named Steps - Transform tab - Pivot column

In values, choose Action

under Advanced options, choose Don't Aggregate

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/BCoMwDMbxVyk9Cy5JB/oc2016EKkScHa0eX8WD4XtUnW3r/AL/DsM1rXgWrwh2sYCINI+HhLeBnSksHCWkKxvahS/qJniNnN6jcJxO7gjHWqzmDUufKSdjoqj8+H0ZzhdCqer4ffizDzyWnBXcNf3P398hixQVftbVKH1/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Steps = _t, Action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", Int64.Type}, {"Steps", type text}, {"Action", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Steps]), "Steps", "Action")
in
    #"Pivoted Column"

[eggyweggs]

Thank you, that worked!