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

[DaGemsta]

Hi guys

This is hard to describe but I'll do my best!

Delivery Date	Truck	Pot1Prod	Pot1Out	Pot2Prod	Pot2Out	Pot3Prod	Pot3Out	Pot4Prod	Pot4Out	Pot5Prod	Pot5Out
19-Oct-23	TRUCK1	2		1		1		1
19-Oct-23	TRUCK1
19-Oct-23	TRUCK1	2	0	2	0	1	0	1	0
19-Oct-23	TRUCK1	2	1933	2	2967	1	0	1	100
19-Oct-23	TRUCK1	2		2		1		1	795
19-Oct-23	TRUCK1	2		2		1		1	757
19-Oct-23	TRUCK1	2		2	1000	1		1
19-Oct-23	TRUCK1	2	0	2	900	1	0	1	100
19-Oct-23	TRUCK1	2		2		1		1	500
19-Oct-23	TRUCK1	2		2		1		1	500
19-Oct-23	TRUCK1	1	4000
19-Oct-23	TRUCK1	2	0	2	107	1	1921	1	0
19-Oct-23	TRUCK1	2	0	2	0	1	0	1	0
20-Oct-23	TRUCK2	1		2		1		2		2
20-Oct-23	TRUCK2	1	0	2	0	2	0	2	0	2	0
20-Oct-23	TRUCK2	1	0	2	2463	2	7044	2	5993	2	4500
20-Oct-23	TRUCK2	1	1500	2		2		2		2
20-Oct-23	TRUCK2	1	513	2		2		2		2
20-Oct-23	TRUCK2	1	0	1	0	1	0	2	0	2	0
20-Oct-23	TRUCK2
20-Oct-23	TRUCK2	1	0	1	1994	1	6006	2	0	2	0
20-Oct-23	TRUCK2	1	0	1	0	1	0	2	0	2	0

I need to go from the above to this

Delivery Date	Truck	1	2
19-Oct-23	TRUCK1	8673	6907
20-Oct-23	TRUCK2	10013	20000

So grouping by Truck and Delivery Date, I need to SUM all the "out" values when the "prod" values are 1 and all the "out" values when the "prod" value is 2.

This will be for over 40 trucks across weeks of delivery days so the same truck will appear on multiple delivery dates.

There are also 6 prod codes in total.

I cannot for the life of me work out how.

All help greatfully recieved!

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[DaGemsta]

Amazing Ashish!!!

Spot on thanks so much 🙂

[Ashish_Mathur]

You are welcome.

[Anonymous]

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.

[DaGemsta]

Hey Yolo 🙂

Wow that's a really clever solution thanks you!

Just one problem.. I'm working with a few thousand rows and on the "Pivoted Column" step it's hanging.

"= Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.2]), "Attribute.2", "Value")"

I've left that step running for over half an hour but it still hasn't completed.

Any ideas how to unstick this step? It seems fine with less rows to work with.

[Anonymous]

Hi @DaGemsta 

If there is more data, the processing will be slow, and the amount of data can be appropriately reduced.

Best Regards!

Yolo Zhu

[DaGemsta]

Hi Yolo

unfortunaetly even after leaving it for 2 hours it never completes 😞