Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Expanded
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.