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 pivot or maybe a merge. I have learned so much trying on the road trying to figure this out. 🙂
What is needed is the Anchor be the column headers for all groups and the value column to be split into a row with the person name. The Statements start at 1 of the Index then repeat. When the index repeats at #1 a new row of values is needed and the Anchor column should be transposed/pivoted (not sure) according to the largest range in the index. That way all expenses are defined in the column headers.
What I have to start with
| Table Index | Group ID | Anchor | Value |
| 1 | 1 | Persons Name 1 | |
| 2 | 1 | Sales | 500,000 |
| 3 | 1 | Advertising | 5,069 |
| 4 | 1 | Amortization | 6,492 |
| 5 | 1 | Bad Debts | 2,829 |
| 6 | 1 | Bank Charges | 2,165 |
| 7 | 1 | Charitable Contributions | 2,515 |
| 8 | 1 | Commissions | 3,176 |
| 9 | 1 | Contract Labor | 6,190 |
| 1 | 2 | Persons Name 2 | |
| 2 | 2 | Sales | 410,000 |
| 3 | 2 | Advertising | 1,563 |
| 4 | 2 | Amortization | 138 |
| 5 | 2 | Bad Debts | 896 |
| 6 | 2 | Bank Charges | 1,310 |
| 7 | 2 | Charitable Contributions | 885 |
| 8 | 2 | Commissions | 482 |
| 9 | 2 | Contract Labor | 1,565 |
| 10 | 2 | Office Expense | 189 |
This is the result that is desired 😀
| ID | Name | Sales | Advertising | Amortization | Bad Debts | Bank Charges | Charitable Contributions | Commissions | Contract Labor | Office Expense |
| 1 | Person Name 1 | 500,000 | 5,069 | 6,492 | 2,829 | 2,165 | 2,515 | 3,176 | 6,190 | |
| 2 | Person Name 2 | 410,000 | 1,563 | 138 | 896 | 1,310 | 885 | 482 | 1,565 | 189 |
The result I currently have (too many columns that repeat unnecessarily and person name 1 should be in the row, not the column header). I could remove the Person Name 1 and reinsert after just not sure where to start or what's the best solution. Side note. My data set contains thousands of rows.
| ID | Person Name 1 | Sales | Advertising | Amortization | Bad Debts | Bank Charges | Charitable Contributions | Commissions | Contract Labor | Person Name 2 | Sales | Advertising | Amortization | Bad Debts | Bank Charges | Charitable Contributions | Commissions | Contract Labor | Office Expense |
| 1 | 500,000 | 5,069 | 6,492 | 2,829 | 2,165 | 2,515 | 3,176 | 6,190 | 500,000 | 5,069 | 6,492 | 2,829 | 2,165 | 2,515 | 3,176 | 6,190 | |||
| 2 | 410,000 | 1,563 | 138 | 896 | 1,310 | 885 | 482 | 1,565 | 410,000 | 1,563 | 138 | 896 | 1,310 | 885 | 482 | 1,565 | 189 |
Thank you from the newpq user
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"
8 Replies
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 Custom2Ok. 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.
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"anhoter way,
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}}), #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Group ID"}, {{"all", each Table.PromoteHeaders(Table.Transpose(Table.Skip(_)[[Anchor],[Value]])){0}}, {"name", each _[Anchor]{0}}}), #"Tabella all espansa" = Table.ExpandRecordColumn(#"Raggruppate righe", "all", {"Sales", "Advertising", "Amortization", "Bad Debts", "Bank Charges", "Charitable Contributions", "Commissions", "Contract Labor", "Office Expense"}, {"Sales", "Advertising", "Amortization", "Bad Debts", "Bank Charges", "Charitable Contributions", "Commissions", "Contract Labor", "Office Expense"}) in #"Tabella all espansa"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"
