Forum Discussion
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...
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"
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