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"

     

reshapedata's avatar
reshapedata
New Member
5 years ago

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. 

 

 

 

artemus's avatar
artemus
Microsoft Employee
5 years ago

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.

  • reshapedata's avatar
    reshapedata
    New Member
    5 years ago

    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