Power Query how to combine multiple ranges and their subtotal information into one range

[Guitarmageddon]

I have a very messy report output that Im trying to consolidate with power query. In its old form, this report was a bunch of summary ranges for different suppliers. It is dynamic and basically just lists a bunch of tables (in range form) of data. The finance folks have just revised the report to now add a supplier number between each table. The users would like to have that supplier brought down into the range of data, for which we will then combine into one table in typical power query fashion. How would I go about doing that?

Here is a snapshot of what it looks like. What I get is some version of the ranges on the left. Various dynamic collections by supplier. The tables nested within could be any number of rows, as you can see. What I am aiming to clean the data into would be the example on the right. Any thoughts?

 

[JGordon11]

With range A1:D20 named Data

let
    Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
    lst = List.Zip(List.Skip(List.Accumulate(Source[Column1], {{0,-1}}, (s,c)=> let ll = List.Last(s) in 
        if c=null then if Number.IsEven(ll{1}) then s & {{ll{0}+1, ll{1}+1}} else s & {{ll{0}, ll{1}+1}} else s & {ll}))){0},
    Custom1 = Table.FromColumns(Table.ToColumns(Source) & {lst}),
    tbl = Table.Group(Custom1, {"Column5"}, {{"All", each _}}),
    tbl1 = Table.TransformColumns(tbl, {"All", each 
        let 
            tbl2 = Table.SelectRows(_, each ([Column1] <> null)),
            tbl3 = Table.AddColumn(tbl2, "Supplier", each tbl2[Column2]{0}),
            tbl4 = Table.AddColumn(tbl3, "Supplier Number", each tbl2[Column2]{1}),
            tbl5 = Table.Skip(tbl4,3),
            tbl6 = Table.RemoveColumns(tbl5,{"Column5"}),
            tbl7 = Table.ReorderColumns(tbl6,{"Supplier", "Supplier Number", "Column1", "Column2", "Column3", "Column4"})
        in 
            Table.RenameColumns(tbl7,{{"Column1", "Color"}, {"Column2", "Description"}, {"Column3", "Cost"}, {"Column4", "Retail"}})
    }),
    Result = Table.Combine(tbl1[All])
in
    Result

produces the table in G1:G7

delme.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Supplier	Supplier A					Supplier	Supplier Number	Color	Description	Cost	Retail
2	Supplier Number	1234					Supplier A	1234	Red	ABC	1	2
3							Supplier B	2345	Blue	DEF	2	3
4	Color	Description	Cost	Retail			Supplier B	2345	Green	GHI	5	8
5	Red	ABC	1	2			Supplier B	2345	Yellow	JKL	3	5
6							Supplier C	3456	Grey	MNO	2	3
7	Supplier	Supplier B					Supplier C	3456	Red	PQR	5	8
8	Supplier Number	2345
9
10	Color	Description	Cost	Retail
11	Blue	DEF	2	3
12	Green	GHI	5	8
13	Yellow	JKL	3	5
14
15	Supplier	Supplier C
16	Supplier Number	3456
17
18	Color	Description	Cost	Retail
19	Grey	MNO	2	3
20	Red	PQR	5	8
21
Sheet5

 

[JGordon11]

A little simpler version

let
    Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddCustom = Table.AddColumn(AddIndex, "Custom", each if [Column1]="Supplier" then [Index] else null),
    FillDown = Table.FillDown(AddCustom,{"Custom"}),
    tbl = Table.Group(FillDown, {"Custom"}, {{"All", each _}}),
    tbl1 = Table.TransformColumns(tbl, {"All", each 
        let 
            tbl2 = Table.SelectRows(_, each ([Column1] <> null)),
            tbl3 = Table.AddColumn(tbl2, "Supplier", each tbl2[Column2]{0}),
            tbl4 = Table.AddColumn(tbl3, "Supplier Number", each tbl2[Column2]{1}),
            tbl5 = Table.Skip(tbl4,3),
            tbl6 = Table.RemoveColumns(tbl5,{"Index", "Custom"}),
            tbl7 = Table.ReorderColumns(tbl6,{"Supplier", "Supplier Number", "Column1", "Column2", "Column3", "Column4"})
        in 
            Table.RenameColumns(tbl7,{{"Column1", "Color"}, {"Column2", "Description"}, {"Column3", "Cost"}, {"Column4", "Retail"}})
    }),
    Result = Table.Combine(tbl1[All])
in
    Result

 

[smozgur]

A little simpler version

let
    Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddCustom = Table.AddColumn(AddIndex, "Custom", each if [Column1]="Supplier" then [Index] else null),
    FillDown = Table.FillDown(AddCustom,{"Custom"}),
    tbl = Table.Group(FillDown, {"Custom"}, {{"All", each _}}),
    tbl1 = Table.TransformColumns(tbl, {"All", each
        let
            tbl2 = Table.SelectRows(_, each ([Column1] <> null)),
            tbl3 = Table.AddColumn(tbl2, "Supplier", each tbl2[Column2]{0}),
            tbl4 = Table.AddColumn(tbl3, "Supplier Number", each tbl2[Column2]{1}),
            tbl5 = Table.Skip(tbl4,3),
            tbl6 = Table.RemoveColumns(tbl5,{"Index", "Custom"}),
            tbl7 = Table.ReorderColumns(tbl6,{"Supplier", "Supplier Number", "Column1", "Column2", "Column3", "Column4"})
        in
            Table.RenameColumns(tbl7,{{"Column1", "Color"}, {"Column2", "Description"}, {"Column3", "Cost"}, {"Column4", "Retail"}})
    }),
    Result = Table.Combine(tbl1[All])
in
    Result

This is very good!

 

[Guitarmageddon]

@JGordon11 Thanks so much for working on this solution. So In my efforts to provide a simple skeleton of the source data I think I may not have done it justice, having a bit of trouble implementing your solution. Now that I have figured out the XL2BB add-in, I think I can provide a much better analog for what the data is. I have included a small sampling below. As you can see, there is the "supplier or party" and the location type info about each little range of data. And this goes on for dozens of variable-length suppliers throughout this sheet...Also a lot of annoying merged cells, but that is easy enough to clear with PQ.
Here is that sample in raw form. The cells 51 and up to the beginning are mostly junk and I would be clearing them out with the query.

InvoiceAging_Payables Invoice Aging Report Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
52	Currency					USD
53
54
55	Supplier or Party			SUPPLIER A SAMPLE
56
57	Supplier Number			1234
58
59	Site			DALLAS						DALLAS			TX
60
61	Invoice Number	Due Date	Days		Percentage			Remaining				1 Month		2 Months			3 Months				Over 3 Months
62			Due		Unpaid			Amount				Overdue		Overdue			Overdue				Overdue
63
64	12345	6/1/23	19		100			-225.00				-225.00		0.00			0.00				0.00
65
66
67	Total for Site DALLAS							-225.00				-225.00		0.00			0.00			0.00
68												100 %		0 %			0 %			0 %
69
70
71	Supplier or Party			SUPPLIER B SAMPLE
72
73	Supplier Number			12948
74
75	Site			DALLAS2						DALLAS			TX
76
77	Invoice Number	Due Date	Days		Percentage			Remaining				1 Month		2 Months			3 Months				Over 3 Months
78			Due		Unpaid			Amount				Overdue		Overdue			Overdue				Overdue
79
80	CN857959	6/15/23	5		100			-45.00				-45.00		0.00			0.00				0.00
81
82	CN858405	6/15/23	5		100			-45.00				-45.00		0.00			0.00				0.00
83
84
85	Total for Site DALLAS2							-90.00				-90.00		0.00			0.00			0.00
86												100 %		0 %			0 %			0 %
87
88
89	Supplier or Party			SUPPLER C
90
91	Supplier Number			291010
92
93	Site			DALLAS3						DALLAS			TX
94
95	Invoice Number	Due Date	Days		Percentage			Remaining				1 Month		2 Months			3 Months				Over 3 Months
96			Due		Unpaid			Amount				Overdue		Overdue			Overdue				Overdue
97
98	CN858044	6/15/23	5		100			-40.00				-40.00		0.00			0.00				0.00
99
100
101	Total for Site DALLAS3							-40.00				-40.00		0.00			0.00			0.00
102												100 %		0 %			0 %			0 %
103
104
105	Supplier or Party			SUPPLIER D
106
107	Supplier Number			303030
108
109	Site			CHICAGO						CHICAGO			IL
110
111	Invoice Number	Due Date	Days		Percentage			Remaining				1 Month		2 Months			3 Months				Over 3 Months
112			Due		Unpaid			Amount				Overdue		Overdue			Overdue				Overdue
113
114	987	6/7/22	378		100			-129.72				0.00		0.00			0.00				-129.72
115
116	9876	6/9/22	376		100			-51.98				0.00		0.00			0.00				-51.98
117
118	98765	6/22/22	363		100			-82.60				0.00		0.00			0.00				-82.60
119
120	109876.1667	6/22/22	363		100			-923.44				0.00		0.00			0.00				-923.44
121
122	158765.1667	7/8/22	347		100			-573.15				0.00		0.00			0.00				-573.15
123
124	207654.1667	7/11/22	344		100			-256.08				0.00		0.00			0.00				-256.08
125
126	232098.6667	7/29/22	326		100			-372.34				0.00		0.00			0.00				-372.34
127
128	280987.6667	8/9/22	315		100			-171.09				0.00		0.00			0.00				-171.09
129
130	329876.6667	8/25/22	299		100			-103.65				0.00		0.00			0.00				-103.65
131
132	354321.1667	10/3/22	260		100			-122.81				0.00		0.00			0.00				-122.81
133
134
135	Total for Site CHICAGO							-12,325.77				0.00		0.00			0.00			-12,325.77
136												0 %		0 %			0 %			100 %
137
138
Sheet1

And just a quick screen snip to show you how it looks up to the start of the data....

 

[Guitarmageddon]

And once brought into power query, initially you can see how all those nasty merged columns shake out. Lots of nulls but in places where filtering a null in one column would omit the data in others... Here I have chunked out the first few examples of data, with the blue being that "header" portion. Im hoping this helps clarify. The columns referencing the months overdue extend well out to the right to columns 27 or so.

 

[smozgur]

Try this.
I edited @JGordon11's beautiful query to adapt the solution to your actual data structure. All credits goes to @JGordon11.

"Data" named range for the sample data: A1:W136
Since many merged cells are in the source file, this might look confusing, but it entirely uses the main logic in @JGordon11's solution. Any change in the data range might make this code unusable.

Notes:

• I also added the Site column as it looks like it is necessary.
• Skipped 54 rows at the beggining according to the new data structure.

let
    Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
    SkipRows = Table.Skip(Source, 54),
    SkipColumns = Table.RemoveColumns(SkipRows,{"Column22", "Column23"}),
    AddIndex = Table.AddIndexColumn(SkipColumns, "Index", 0, 1, Int64.Type),
    AddCustom = Table.AddColumn(AddIndex, "Custom", each if [Column1]="Supplier or Party" then [Index] else null),
    FillDown = Table.FillDown(AddCustom,{"Custom"}),
    tbl = Table.Group(FillDown, {"Custom"}, {{"All", each _}}),
    tbl1 = Table.TransformColumns(tbl, {"All", each 
        let 
            tbl2 = Table.SelectRows(_, each ([Column1] <> null and not Text.Contains(Text.From([Column1]), "Total"))),
            tbl3 = Table.AddColumn(tbl2, "Supplier or Party", each tbl2[Column4]{0}),
            tbl4 = Table.AddColumn(tbl3, "Supplier Number", each tbl2[Column4]{1}),
            tbl5 = Table.AddColumn(tbl4, "Site", each tbl2[Column4]{2}),
            tbl6 = Table.Skip(tbl5,4),
            tbl7 = Table.RemoveColumns(tbl6,{"Index", "Custom"})
        in 
            tbl7
    }),
    CombineAll = Table.Combine(tbl1[All]),
    RenameColumns = Table.RenameColumns(CombineAll,{{"Column1", "Invoice Number"}, {"Column2", "Due Date"}, {"Column3", "Days Due"}, {"Column5", "Percentage Update"}, {"Column8", "Remaining Amount"}, {"Column12", "1 Month Overdue"}, {"Column14", "2 Months Overdue"}, {"Column17", "3 Months Overdue"}, {"Column21", "Over 3 Months Overdue"}}),
    RemoveColumns = Table.RemoveColumns(RenameColumns,{"Column4", "Column6", "Column7", "Column9", "Column10", "Column11", "Column13", "Column15", "Column16", "Column18", "Column19", "Column20"}),
    ReorderColumns = Table.ReorderColumns(RemoveColumns,{"Supplier or Party", "Supplier Number", "Site", "Invoice Number", "Due Date", "Days Due", "Percentage Update", "Remaining Amount", "1 Month Overdue", "2 Months Overdue", "3 Months Overdue", "Over 3 Months Overdue"}),
    Result = Table.TransformColumnTypes(ReorderColumns,{{"Supplier or Party", type text}, {"Supplier Number", type text}, {"Site", type text}, {"Invoice Number", type text}, {"Due Date", type date}, {"Days Due", Int64.Type}, {"Remaining Amount", Currency.Type}, {"1 Month Overdue", Currency.Type}, {"2 Months Overdue", Currency.Type}, {"3 Months Overdue", Currency.Type}, {"Over 3 Months Overdue", Currency.Type}, {"Percentage Update", type number}})     
in
    Result

 

[Guitarmageddon]

Wow! Superstars @smozgur and @JGordon11

Playing with this implementation and will maybe have a question or two momentarily...... Digging in to the "skip columns" thing now, as there seems to be some data at the end of the spreadsheet I did not see. But I suspect that could be an unneeded totals column down at the very very end of the data

 

[smozgur]

I only made simple edits in the existing query while @JGordon11 was not around There is only one superstar in this thread - @JGordon11!

Playing with this implementation and will maybe have a question or two momentarily...... Digging in to the "skip columns" thing now, as there seems to be some data at the end of the spreadsheet I did not see. But I suspect that could be an unneeded totals column down at the very very end of the data

In the meantime, please don't forget to mark the initial answer (post #3) as the solution to this question as it will also help future readers.

 

[Guitarmageddon]

Ok I went in and happily marked that as a solution. This is going to be something very powerful to use when I have really annoying grouped source data in this fashion. And there are others...

Using the indexing function with the grouping like you did taught me a lot, thats huge! Thanks again.