Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
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
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}?}})