Multiple Columns to rows. Rows are inconsistent ranges and are indexed.

[reshapedata]

*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

[Anonymous]

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"

[Anonymous]

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"

[Anonymous · Accepted 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"

[artemus]

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

[reshapedata]

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]

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]

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

[reshapedata]

Ok. This looks great. I will try and plug it in and give you some feedback asap. Thank you very much.