Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
*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
Solved! Go to Solution.
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 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"
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"
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
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
Ok. This looks great. I will try and plug it in and give you some feedback asap. Thank you very much.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.