Pivoting in PQ

[smko]

Original Table

Date	ID	Qty	Volume
1/1/2021	1	50	1000
1/1/2021	2	70	2000
2/1/2021	1	40	800
2/1/2021	2	60	1500
2/1/2021	3	10	200

First expected result (by qty)

ID	1/1/2021	2/1/2021
1	50	40
2	70	60
3		10

Second expected result (by qty and volume)

ID	1/1/2021	1/1/2021	2/1/2021	2/1/2021
1	50	1000	40	800
2	70	2000	60	1500
3			10	200

I wish to do this in power query to perform further transformation, basically the grain must be per unique id. How can I achieve this?

[Mohammad_Refaei · Accepted solution]

If you don't want to write long M Queries then you can follow simple steps with the Power Query GUI.

The fisrt table is already created so I won't highlight it.

For the second one, you may select both Qty & Volume columns and in Power Query go to Transform --> Unpivot.

Select your date column and change it to Text

Then Select the Date and Attribute columns, right click any of them then Merge Columns them

Then Pivot the Merged column on the value field

[smko]

Wow this is some voodoo magic right there, thanks!

[Jakinta]

Here is the query to complete for 1st task. By Qty. You can do the same for Volume if you replace "Qty" w/ "Volume".

For the second task you have to change the column names for Volume query and combine them. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lECYVMDEMPAwEApVgdF0giIzUGSRjBJI1SdJiBJC0w5kEYzsKmmmJLGYNsgpirFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Qty = _t, Volume = _t]),
    Grouped = Table.Group(Source, {"ID"}, {{"Gr", each let t= Table.Pivot(_, List.Distinct(_[Date]), "Date", "Qty"), names=Table.ColumnNames(t), cols=Table.ToColumns(t) in Table.FromColumns(List.RemoveFirstN(List.Transform(cols, each List.RemoveNulls(_)), 2), List.RemoveFirstN(names, 2))}}),
    Expanded = Table.ExpandTableColumn(Grouped, "Gr", Table.ColumnNames(Grouped[Gr]{0}))
in
    Expanded

[smko]

Oh my.. this looks like extremely hard M code to write.

Edited: Ok so I took your concept and make the code simpler. What do you think about my code?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lECYVMDEMPAwEApVgdF0giIzUGSRjBJI1SdJiBJC0w5kEYzsKmmmJLGYNsgpirFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Qty = _t, Volume = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Qty", Int64.Type}, {"Volume", Int64.Type}}),
    #"Removed Vol" = Table.RemoveColumns(#"Changed Type",{"Volume"}),
    #"Pivoted Vol" = let remove_qty = Table.RemoveColumns(#"Changed Type",{"Qty"}) in Table.Pivot(remove_qty, List.Distinct(#"Removed Vol"[Date]), "Date", "Volume"),
    #"Pivoted Qty" = Table.Pivot(#"Removed Vol", List.Distinct(#"Removed Vol"[Date]), "Date", "Qty"),
    #"Pivoted Qty Vol" = Table.NestedJoin(#"Pivoted Vol",{"ID"},#"Pivoted Qty",{"ID"},"Volume"),
    #"Expanded Volume" = Table.ExpandTableColumn(#"Pivoted Qty Vol", "Volume", {"1/1/2021", "2/1/2021"}, {"Volume.1/1/2021", "Volume.2/1/2021"})
in
    #"Expanded Volume"