Help with Data transformation and grouping

[cerobbins]

Hello,

I was hoping someone could help me with some data transformation. 

My source data looks like the following:

Batch	Process	Products	Supplier	Location
A1	start	100	BA20	1
A1	end	80	BA20	1
A2	start	100	BA20	7
A2	qc	90	BA20	7
A2	test	85	BA20	7
A2	end	80	BA20	7
B1	start	200	BC55	2
B1	qc	150	BC55	2
B1	test	125	BC55	2
B1	end	100	BC55	2
C1	start	500	FB12	5
C1	qc	495	FB12	5
C1	end	490	FB12	5

my desired transformation output looks like the below:

Batch	Supplier	Location	Start	QC	Test	End
A1	BA20	1	100	null	null	80
A2	BA20	7	100	90	85	80
B1	BC55	2	200	150	125	100
C1	FB12	5	500	495	null	490

I'm struggling to arrive at the desired result using power query alone. I'm hoping that someone could show me the appropriate steps to make this transformation.

Cheers!

[Jimmy801]

Hello @cerobbins 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Jimmy801 · Accepted solution]

Hello @cerobbins 

Table.Pivot is the function you need

let
	Source = #table
	(
		{"Batch","Process","Products","Supplier","Location"},
		{
			{"A1","start","100","BA20","1"},	{"A1","end","80","BA20","1"},	{"A2","start","100","BA20","7"},	{"A2","qc","90","BA20","7"},	{"A2","test","85","BA20","7"},	
			{"A2","end","80","BA20","7"},	{"B1","start","200","BC55","2"},	{"B1","qc","150","BC55","2"},	{"B1","test","125","BC55","2"},	{"B1","end","100","BC55","2"},	
			{"C1","start","500","FB12","5"},	{"C1","qc","495","FB12","5"},	{"C1","end","490","FB12","5"}
		}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Products", Int64.Type}}),
    Pivot = Table.Pivot(ChangeType, List.Distinct(ChangeType[Process]), "Process", "Products", List.Sum)
in
	Pivot

Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[artemus]

It would look something like:

Table.Group(PreviousStep, {"Batch", "Supplier", "Location"}, {{"Start", each Table.SelectRows(_, each [Process] = "start")[Products]{0}? }, {"QC", each Table.SelectRows(_, each [Process] = "qc")[Products]{0}?}, {"Test", each Table.SelectRows(_, each [Process] = "test")[Products]{0}?}, {"End", each Table.SelectRows(_, each [Process] = "end")[Products]{0}?}})