cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smko
Frequent Visitor

Pivoting in PQ

Original Table

DateIDQtyVolume
1/1/20211501000
1/1/20212702000
2/1/2021140800
2/1/20212601500
2/1/2021310200

 

First expected result (by qty)

ID1/1/20212/1/2021
15040
27060
3 10

 

Second expected result (by qty and volume)

ID1/1/20211/1/20212/1/20212/1/2021
150100040800
2702000601500
3  10200

 

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?

1 ACCEPTED SOLUTION
Mohammad_Refaei
Solution Specialist
Solution Specialist

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.

m_refaei_0-1627010319743.png

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

Screenshot (37).png

Then Pivot the Merged column on the value field

m_refaei_1-1627010690721.png

 

View solution in original post

4 REPLIES 4
Mohammad_Refaei
Solution Specialist
Solution Specialist

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.

m_refaei_0-1627010319743.png

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

Screenshot (37).png

Then Pivot the Merged column on the value field

m_refaei_1-1627010690721.png

 

View solution in original post

Wow this is some voodoo magic right there, thanks!

Jakinta
Solution Sage
Solution Sage

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
Frequent Visitor

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"

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors