Pivot multiple columns and sum values base on multiple columns in m query

Hi DaGemsta 

You can put the following code to Advanded Editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVLNCsIwDH6XnjdI0j9y9uhBGHoaO/kKvj+2uOpWuy6dCAkJpV+S70vGUSH3l/ujJ606dR1upzOGhIIHw1KINnUbwJptgmI3WETM4i4YWes5JXY+K4EgKlKk7dkex1rfgA1TgmrSeikbQy7cL6ztf7HRDbz/HbwUhLRoZEplRXNLbo0gA9OH5pr0UokqEgrt11EEJ+PSsXswZk4tc3o1cQXVUvhaEn0TkPCwqI+DS5oLRaiZqCcymzl1AK5d/p3Bpyc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Delivery Date" = _t, Truck = _t, Pot1Prod = _t, Pot1Out = _t, Pot2Prod = _t, Pot2Out = _t, Pot3Prod = _t, Pot3Out = _t, Pot4Prod = _t, Pot4Out = _t, Pot5Prod = _t, Pot5Out = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Delivery Date", type date}, {"Truck", type text}, {"Pot1Prod", Int64.Type}, {"Pot1Out", Int64.Type}, {"Pot2Prod", Int64.Type}, {"Pot2Out", Int64.Type}, {"Pot3Prod", Int64.Type}, {"Pot3Out", Int64.Type}, {"Pot4Prod", Int64.Type}, {"Pot4Out", Int64.Type}, {"Pot5Prod", Int64.Type}, {"Pot5Out", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Delivery Date", "Truck", "Pot1Prod", "Pot1Out", "Pot2Prod", "Pot2Out", "Pot3Prod", "Pot3Out", "Pot4Prod", "Pot4Out", "Pot5Prod", "Pot5Out"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Delivery Date", "Truck"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 4}, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Table.RowCount(Table.SelectRows(#"Added Index",(x)=>x[Attribute.2]=[Attribute.2] and x[Index]<=[Index]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Attribute.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.2]), "Attribute.2", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Delivery Date", "Truck", "Prod"}, {{"Sum", each List.Sum([Out]), type nullable number}}),
    #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Prod", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Prod", type text}}, "en-US")[Prod]), "Prod", "Sum"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column1",{"0"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Delivery Date", "Truck", "1", "2"})
in
    #"Reordered Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.