That part is not very easy to follow, because it occurs within the transform function (each) in the
TransformColumns() function, so you don't have steps to watch what's going on in it. However, there is a way exposing that part, so you can make that function work with a single embedded table and see what's going on. (There should be other ways, perhaps creating the function as a standalone query and run it with a parameters. This is what I personally prefer).
So, you have this at the tbl step:
The transform function within the TransformColumns() function on step tbl1, iterates through the All column rows and transform Table values to get the desired result eventually.
Duplicate the query, and delete everything after step tbl.
After the tbl step in the advanced editor, extract one table from the column All by using this expression: tbl[All]{0}
(this means, take the first cell from the column A of the tbl table). This is the first section data. Now you can process only this section in the main query, apply the tbl2 through tbl7, and you get all steps in the Applied Steps list.
Here is the final query for debugging purposes.
Power Query:
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 _}}),
OneRow = tbl[All]{0},
tbl2 = Table.SelectRows(OneRow, 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
Now you can see tbl2 (section data table), and tbl2[Column4] (4th column = Column D in the worksheet), and what {0}, {1}, and {2} means. They are the row numbers and used to get values for the Supplier or Party, Supplier Number, and Site values for that section. In tbl3, tbl4, and tbl5 steps, the code is adding three columns containing these values.
Take a look at the table at the tbl2 step, the first four rows contain the "meta" data for the section. Since you already got the necessary values from those rows (tbl3, tbl4, tbl5), now it is time to remove those rows (including the list items header row) and get the necessary list item rows after those meta rows. It happens in the tbl6 step with
Table.Skip() function that takes number of rows to be skipped. In the following screenshot, I scrolled right to show the newly added columns.
Then finally, the tbl7 step is to remove the unnecessary Index and Custom columns.
In the TransformColumns() function, this processes will be repeated for each tables, and each Table value in tbl will be transformed in the tbl7 step. You can see the transformation result by clicking on the Table cells (not on the Table link in the cell, but some blank space in the cell) in the tbl1 step in the original query:
The rest should be straightforward, combine all tables in the All column (CombineAll), rename the columns (RenameColumns), remove unnecessary columns (RemoveColumns), and change the order of the columns as desired (ReorderColumns).
The final step, Result, takes care about the column data types.
EDIT: coming back to this one now... i think since the first step removes all the nulls, you are left with 3 lines in column 4. And the 0, 1, 2, signifies the FIRST, SECOND, and THIRD line of data. That piece is then moved into their own respective new column for each line of data within that little code loop. Do I have that right?
Yes, you do. I hope what I wrote above helps more as it will be hopefully useful for future readers as well.