Forum Discussion
Multiple Columns to rows. Rows are inconsistent ranges and are indexed.
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"
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
Ok. This looks great. I will try and plug it in and give you some feedback asap. Thank you very much.
Hi Artemus. Just wanted to give you a small update. So far on my side, the result has been the column still runs on and I get many errors - too many enumerations...This is most likely something I did or maybe I just need to replace some nulls with blanks so I think I can deal with the errors but the headers have me stumped for now. I'll run through all the edits make sure I didn't miss anything and let you know the result asap. Thanks again.
If the table is large this may cause an issue, as it probably can't send it back to the server as a native query.
I think your point regarding the size is the case. I think referencing the query to separate the anchor and pivot as headers and then pivoting the values to join tables would be another method to achieve the same goal. Not sure but that's what I will try next. Learning so much trying to figure this out 🙂
Many thanks again
