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 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!

 

 

TypeTray 1Tray 2Tray 3Total
Defect 1361726
Defect 235715
Defect 31331531
Defect 416161345
Defect 5221519
Defect 657315
Defect 7183425
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

4 Replies

  • 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"
  • baghdadi62's avatar
    baghdadi62
    Icon for Resolver III rankResolver III
    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