Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Columns to Rows Data Modelling Challenge

Hi - I think I'm close but stuck on final hurdle. I have used a combination of UnPivot, and Re-Pivot to get me there.    I have a data set that looks like this (3 related fields for Product, Unit P...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Hi Anonymous 

 

Try this. It works with your sample data

 

let
    Source = Excel.CurrentWorkbook(){[Name="Original_DataSet"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User Name", type text}, {"User ID", Int64.Type}, {"Product 1", type text}, {"Unit Price 1", type number}, {"Product 1 Tax Code", type text}, {"Product 2", type text}, {"Unit Price 2", type number}, {"Product 2 Tax Code", type text}, {"Product 3", type text}, {"Unit Price 3", type number}, {"Product 3 Tax Code", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"User Name", "User ID"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each Text.Select([Attribute],{"0".."9"})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Remove([Attribute],{"0".."9"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "Value")
in
    #"Pivoted Column"