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?

 

[Guitarmageddon]

@smozgur I was going back through the code this morning rejoicing it all works, but trying to dissect and understand the middle portion better, for future replication...

In this part:

    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
    }),

I see that column 1 is being used to remove that unneeded "totals" row, and also to get rid of null data. Its the three rows after that Im unclear on. A column is added and its called "suppler or party" etc, for each row in tbl12 step, but Im confused on the {0} , {1} , {2} parts. What is that directing the code to do?

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?

 

[smozgur]

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.

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.