Forum Discussion

reshapedata's avatar
reshapedata
New Member
5 years ago
Solved

Multiple Columns to rows. Rows are inconsistent ranges and are indexed.

*Edited the Tables to make it easier on the eyes.   I have the below data sample set which was a lot of work for me to get this far (reshaping and cleansing were not easy for me). It feels like a p...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    just a variant of some of the solutions already proposed, , but perhaps less difficult to follow.

     

    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFNT8MgGID/CumZA7wtjB51ejNq4rHZgW5sI65gAI3x18tHh241HpqmPE95y9NhaGiD8/WsnLfGo0c5KZQWULPBQwMzfpEn5eOdEYIJIZm1M7vZfSgXtNfmkAxMeJ95d+aTjfhLBm1NfOS46yELbBZu5Q7dqTGkAYAFlNd5peYVrY/SHVQRKGdZWM1CYjrI8aTQ2prg9PieRhWZ0SKLs2ynSXs/8xbTFc+8rzzuILcBPcjRuvy5tC/nTRiuU8FFKviVqqOXqWCRimLG25oKlqloK2oouAolel4zwTITxS0lNRP8n0mIn0iwiNQJqIngr0TpHGWDOLI4T/u93ip0//mmjFfJEfGvbr4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Table Index" = _t, #"Group ID" = _t, Anchor = _t, Value = _t]),
    #"Rimosse colonne" = Table.RemoveColumns(Origine,{"Table Index"}),
        #"Modificato tipo" = Table.TransformColumnTypes(#"Rimosse colonne",{ {"Group ID", Int64.Type}, {"Anchor", type text}, {"Value", type number}}),
    ttr=Table.FromRecords(Table.TransformRows(#"Modificato tipo", (row) => row & (if row[Value]=null then [Anchor = "Name", Value=row[Anchor]] else row))),
    #"Colonna trasformata tramite Pivot" = Table.Pivot(ttr, List.Distinct(ttr[Anchor]), "Anchor", "Value", (x)=>try x{0} otherwise null)
in
    #"Colonna trasformata tramite Pivot"

     

    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFNT8MgGID/CumZA7wtjB51ejNq4rHZgW5sI65gAI3x18tHh241HpqmPE95y9NhaGiD8/WsnLfGo0c5KZQWULPBQwMzfpEn5eOdEYIJIZm1M7vZfSgXtNfmkAxMeJ95d+aTjfhLBm1NfOS46yELbBZu5Q7dqTGkAYAFlNd5peYVrY/SHVQRKGdZWM1CYjrI8aTQ2prg9PieRhWZ0SKLs2ynSXs/8xbTFc+8rzzuILcBPcjRuvy5tC/nTRiuU8FFKviVqqOXqWCRimLG25oKlqloK2oouAolel4zwTITxS0lNRP8n0mIn0iwiNQJqIngr0TpHGWDOLI4T/u93ip0//mmjFfJEfGvbr4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Table Index" = _t, #"Group ID" = _t, Anchor = _t, Value = _t]),
    #"Rimosse colonne" = Table.RemoveColumns(Origine,{"Table Index"}),
        #"Modificato tipo" = Table.TransformColumnTypes(#"Rimosse colonne",{ {"Group ID", Int64.Type}, {"Anchor", type text}, {"Value", type number}}),
    ttr=Table.FromRecords(Table.TransformRows(#"Modificato tipo", (row) => row & (if row[Value]=null then [Anchor = "Name", Value=row[Anchor]] else []))),
    #"Colonna trasformata tramite Pivot" = Table.Pivot(ttr, List.Distinct(ttr[Anchor]), "Anchor", "Value", (x)=>try x{0} otherwise null)
in
    #"Colonna trasformata tramite Pivot"

     

artemus's avatar
artemus
Microsoft Employee
5 years ago

Here is an example (use the advanced editor):

Replace the Source/changed type step with your data source (also note that Custom1 references that step, so if you use a different name, update that too).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFNT8MgGID/CumZA7wtjB51ejNq4rHZgW5sI65gAI3x18tHh241HpqmPE95y9NhaGiD8/WsnLfGo0c5KZQWULPBQwMzfpEn5eOdEYIJIZm1M7vZfSgXtNfmkAxMeJ95d+aTjfhLBm1NfOS46yELbBZu5Q7dqTGkAYAFlNd5peYVrY/SHVQRKGdZWM1CYjrI8aTQ2prg9PieRhWZ0SKLs2ynSXs/8xbTFc+8rzzuILcBPcjRuvy5tC/nTRiuU8FFKviVqqOXqWCRimLG25oKlqloK2oouAolel4zwTITxS0lNRP8n0mIn0iwiNQJqIngr0TpHGWDOLI4T/u93ip0//mmjFfJEfGvbr4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Table Index" = _t, #"Group ID" = _t, Anchor = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Table Index", Int64.Type}, {"Value", Int64.Type}, {"Anchor", type text}, {"Group ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group ID"}, {{"Name", each _{[Value = null]}[Anchor], type text}, {"Group", each let tbl = Table.SelectRows([[Value], [Anchor]], each [Value] <> null) in Table.Pivot(tbl, List.Distinct(tbl[Anchor]), "Anchor", "Value"), type table}}),
    Custom1 = Table.ExpandTableColumn(#"Grouped Rows", "Group", List.Distinct(Table.SelectRows(#"Changed Type", each [Value] <> null)[Anchor])),
    Custom2 = Table.TransformColumnTypes(Custom1, List.Transform(Table.ColumnNames(Table.RemoveColumns(Custom1, {"Group ID", "Name"})), each {_, type number}))
in
    Custom2

 

reshapedata's avatar
reshapedata
New Member
5 years ago

Ok. This looks great. I will try and plug it in and give you some feedback asap. Thank you very much.