Way to transform data from a pivoted like format into a more structured format

[lazer1]

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

[baghdadi62 · Accepted 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.

Unpivot Step

All steps:

All steps

[lazer1]

Thank you!!!

[baghdadi62]

I'm glad I could help.

[Papermain]

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"