March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I am trying to figure out a way to use power query to transform a table of data into another. See pic below. I am trying to turn the table on the left into the table on the right. I was having trouble finding the right way to describe this, and was having a hard time finding if there were any already posted similar solutions. Any help is appreciated. Thanks!
Type | Tray 1 | Tray 2 | Tray 3 | Total |
Defect 1 | 3 | 6 | 17 | 26 |
Defect 2 | 3 | 5 | 7 | 15 |
Defect 3 | 13 | 3 | 15 | 31 |
Defect 4 | 16 | 16 | 13 | 45 |
Defect 5 | 2 | 2 | 15 | 19 |
Defect 6 | 5 | 7 | 3 | 15 |
Defect 7 | 18 | 3 | 4 | 25 |
Solved! Go to Solution.
1- Remove "Total" Column.
2- Right mouse click on Type column and select "Unpivot Other Columns".
3- Replace "Tray" With Empty String on "Attribute" Column.
All steps:
1- Remove "Total" Column.
2- Right mouse click on Type column and select "Unpivot Other Columns".
3- Replace "Tray" With Empty String on "Attribute" Column.
All steps:
Thank you!!!
I'm glad I could help.
let
Source = Excel.CurrentWorkbook(){[Name="DefectTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Tray 1", Int64.Type}, {"Tray 2", Int64.Type}, {"Tray 3", Int64.Type}, {"Total", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Type"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Defect count"}}),
#"Renamed Columns" = Table.RenameColumns(#"Renamed Columns1",{{"Attribute", "Tray"}}),
#"Lowercased Text" = Table.TransformColumns(#"Renamed Columns",{{"Tray", Text.Lower, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Lowercased Text","tray","",Replacer.ReplaceText,{"Tray"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Tray] <> "total")),
#"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"Tray", Text.Trim, type text}})
in
#"Trimmed Text"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
55 | |
27 | |
17 | |
12 |