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"

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Here using the Record.FromTable function (which I had never used or seen used in the forum) can come in handy.

 

 

 

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]),
    #"Rinominate colonne" = Table.RenameColumns(Origine,{{"Anchor", "Name"}}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Rinominate colonne",{ {"Group ID", Int64.Type}, {"Name", type text}, {"Value", type number}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Group ID"}, {{"Name", each Record.FromTable(Table.Skip(_))},{"name", each _[Name]{0}}}),
    #"Tabella Name espansa" = Table.ExpandRecordColumn(#"Raggruppate righe", "Name", {"Sales", "Advertising", "Amortization", "Bad Debts", "Bank Charges", "Charitable Contributions", "Commissions", "Contract Labor", "Office Expense"})
in
    #"Tabella Name espansa"