Forum Discussion

luisecp's avatar
luisecp
Frequent Visitor
2 years ago

Convert a column with data in multiple row to columns

How convert a rows with data associate a the key in columns

 

NameDATA
Luis20
LuisUSA
Luisspanish
Erick24
ErickUK
ErickEnglish

 

Convert to:

 

nameagecountrylanguage
Luis20USAspanish
Erick24UKenglish

 

Need Help

1 Reply

  • danextian's avatar
    danextian
    Super User
    2 years ago

    HI luisecp ,

     

    Assuming that there are only three rows per name, try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8inNLFbSUTIyUIrVgfNCgx2RucUFiXmZxRlgIdeizORskAYTFG6oNwrXNS89B6wjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, DATA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"DATA", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}})[Modulo]), "Modulo", "DATA"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"0", "age"}, {"1", "country"}, {"2", "language"}})
in
    #"Renamed Columns"