Forum Discussion

lazer1's avatar
lazer1
New Member
3 years ago
Solved

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

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 ha...
Need Help
  • baghdadi62's avatar
    baghdadi62
    3 years ago

    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

Papermain's avatar
Papermain
Frequent Visitor
3 years ago
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"