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

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"

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"

Forum Discussion

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

Related content

Multiple conditions Index

Index multiple collumns

Index multiple subgroups

Index

Fixed Index Column