Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data transformation columns to row values

Hi Guys, 

 

I need some help with the transformation of my data. 

I have an complex excel file which i simplify in the image below. 

In the current situation I have some column values which i need to get converted to rows. 

See the image below, i thought it was possible within Powerquery. please help. 

 

Need Help
Show and Tell

2 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    1. You can do it inside of Excel with Copy and Transpose.
    2. Or with Power Query inside of Excel or Power BI Desktop like this:

     

    // Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JY67DcAgDAV3cU0RvoY2Cl2UBRD7rxGfaU7Pz4fFWvJMCVLUMIqhXgbNhk4FlCpWQ0LNssOS72V9ncLkBkiRLkcSYwO5n5GH93QJK/lNVEWofp5Dg6Qskn9HZe8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t, #"8" = _t, #"9" = _t, #"10" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"8", Int64.Type}, {"9", Int64.Type}, {"10", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Country"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}})
in
    #"Renamed Columns"

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)