PowerQuery Pivot Table

[DennisHo]

Hello all,

I would like to restructure the following table: (in reality more than 1k rows with different sold to partys)

Sold to party	Period	Material	MBR Category	Value	Quantity
17012097	4	1	Sales	100	2
17012097	4	1	SRD	20	0
17012097	4	1	Cost	30	0
17012097	5	2	Sales	200	3
17012097	6	2	Sales	300	4

The result should look like this:

Sold to party	Period	Material	Quantity	Sales	SRD	Cost
17012097	4	1	2	100	20	30
17012097	5	2	3	200
17012097	6	2	4	300

As you can see I would like to pivot the column MBR Category and summarize its values. However, when I try to do that, the resulting table still has five rows and not three rows and the SRD/Cost/Sales are not summarized in one row but still show up in different rows.

Do you have any idea, how I could solve this?

Thank you very much in advance.

Best regards

[Vijay_A_Verma · Accepted solution]

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("i45WMjQ3MDQysDRX0lEyAWJDIA5OzEktBrENDICkkVKsDnZlQS4gaZAaA1xqnPOLS4CUMVZFpmDTEfYZge0zRldmhqbMGKzMRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sold to party" = _t, Period = _t, Material = _t, #"MBR Category" = _t, Value = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sold to party", Int64.Type}, {"Period", Int64.Type}, {"Material", Int64.Type}, {"MBR Category", type text}, {"Value", Int64.Type}, {"Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Sold to party", "Period", "Material"}, {{"Quantity", each List.Sum([Quantity]), type nullable number}, {"Temp", each _, type table [Sold to party=nullable number, Period=nullable number, Material=nullable number, MBR Category=nullable text, Value=nullable number, Quantity=nullable number]}}),
    //Function Start
    fxProcess=(Tbl)=>
        let
            #"Removed Columns" = Table.RemoveColumns(Tbl,{"Sold to party", "Period", "Material", "Quantity"}),
            #"Transposed Table" = Table.Transpose(#"Removed Columns"),
            #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
        in
            #"Promoted Headers",
    //Function End
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Temp"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Sales", "SRD", "Cost"}, {"Sales", "SRD", "Cost"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Sold to party", Int64.Type}, {"Period", Int64.Type}, {"Material", Int64.Type}, {"Quantity", Int64.Type}, {"Sales", Int64.Type}, {"SRD", Int64.Type}, {"Cost", Int64.Type}})
in
    #"Changed Type1"

[DennisHo]

Hello both,

both solutions work perfectly fine, I am currently testing everything, but topic should be solved.

Thank you very much!

[wdx223_Daniel · Accepted solution]

= let cols={"Sold to party","Period","Material"} in Table.FromRecords(Table.Group(Source,cols,{"n",each Record.SelectFields(_{0},cols)&Record.FromList({List.Sum([Quantity])}&[Value],{"Quantity"}&[MBR Category])})[n],cols&{"Quantity"}&List.Distinct(Source[MBR Category]),2)

[Vijay_A_Verma · Accepted solution]

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("i45WMjQ3MDQysDRX0lEyAWJDIA5OzEktBrENDICkkVKsDnZlQS4gaZAaA1xqnPOLS4CUMVZFpmDTEfYZge0zRldmhqbMGKzMRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sold to party" = _t, Period = _t, Material = _t, #"MBR Category" = _t, Value = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sold to party", Int64.Type}, {"Period", Int64.Type}, {"Material", Int64.Type}, {"MBR Category", type text}, {"Value", Int64.Type}, {"Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Sold to party", "Period", "Material"}, {{"Quantity", each List.Sum([Quantity]), type nullable number}, {"Temp", each _, type table [Sold to party=nullable number, Period=nullable number, Material=nullable number, MBR Category=nullable text, Value=nullable number, Quantity=nullable number]}}),
    //Function Start
    fxProcess=(Tbl)=>
        let
            #"Removed Columns" = Table.RemoveColumns(Tbl,{"Sold to party", "Period", "Material", "Quantity"}),
            #"Transposed Table" = Table.Transpose(#"Removed Columns"),
            #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
        in
            #"Promoted Headers",
    //Function End
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Temp"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Sales", "SRD", "Cost"}, {"Sales", "SRD", "Cost"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Sold to party", Int64.Type}, {"Period", Int64.Type}, {"Material", Int64.Type}, {"Quantity", Int64.Type}, {"Sales", Int64.Type}, {"SRD", Int64.Type}, {"Cost", Int64.Type}})
in
    #"Changed Type1"