Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WencyREN
Frequent Visitor

Create pivot table using Power query

Spoiler
 

Dear, I have a dataset like below in PBI,  how do I create a table in Power query like the pivot table below, in which calculates the sum of sales value for each distinct product type A/B/C/D? (will need to consider adding other dimensions as well when proceeding further)

Thank you!

WencyREN_0-1653399986835.png

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Select Product column - Home menu - Group by

Under Operation, select Sum and under Column, select Sales value

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKTQGSjiCmLxCbKsXqQCQiU3Ny8stBck5Awg+ILcFyIJ57UWpqHpB2cgYS/kBsaASWc4Yb6OIGJAKA2AQu4V+UmJeeCtLtASQCgdhcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Color = _t, Country = _t, City = _t, #"Sales value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Color", type text}, {"Country", type text}, {"City", type text}, {"Sales value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Sum of Sales value", each List.Sum([Sales value]), type nullable number}})
in
    #"Grouped Rows"

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

Select Product column - Home menu - Group by

Under Operation, select Sum and under Column, select Sales value

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKTQGSjiCmLxCbKsXqQCQiU3Ny8stBck5Awg+ILcFyIJ57UWpqHpB2cgYS/kBsaASWc4Yb6OIGJAKA2AQu4V+UmJeeCtLtASQCgdhcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Color = _t, Country = _t, City = _t, #"Sales value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Color", type text}, {"Country", type text}, {"City", type text}, {"Sales value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Sum of Sales value", each List.Sum([Sales value]), type nullable number}})
in
    #"Grouped Rows"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors