Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with Data transformation and grouping

Hello,

 

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

 

My source data looks like the following:

BatchProcessProductsSupplierLocation
A1start100BA201
A1end80BA201
A2start100BA207
A2qc90BA207
A2test85BA207
A2end80BA207
B1start200BC552
B1qc150BC552
B1test125BC552
B1end100BC552
C1start500FB125
C1qc495FB125
C1end490FB12

5

 

my desired transformation output looks like the below:

BatchSupplierLocationStartQCTest End
A1BA201100nullnull80
A2BA207100908580
B1BC552200150125100
C1FB125500495null

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!

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    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

3 Replies

  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago

    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}?}})
  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

    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