Forum Discussion
Pivoting in PQ
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?
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
4 Replies
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
Wow this is some voodoo magic right there, thanks!
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 ExpandedOh 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"
