Preparing data with rows to remove that are repeated through the data set.|Power Query|Excel Forum|My Online Training Hub

Hello, this is somewhat like Mynda’s Youtube video intro to PQ, but a bit more complicated.

I have a large number of  worksheets that need to be brought into PQ and consolidated into 1 data set that is in proper pivot table format.  Originally, there were about a dozen tables, and I figured out how to delete the rows of redundant data and merge columns/transpose and set into pivot table format. I just copied that M-Code into the other remaining (11) tables and then I could merge the data and set up measures, etc.

That has morphed into many more spreadsheets – it could grow.  So what I want to do is to import all of the worksheets into PQ and merge them into 1 set and then remove the redundant data preventing me from consolidating everything into a pivot-able format.  It’s getting ugly.

I am attaching 2 links to screen shots of the data structure with abbreviated/representative data to illustrate.  The first one is a shot of what all my consolidated raw tables will look like:

https://photos.app.goo.gl/e8d4.....59J25JVh67

Notes:

1. I have about (20) cost centers (shown as #1001-#1020).  Each of these can have – literally – hundreds of related general ledger expense accounts (shown as #101 - #999)
2. For each table there are (7) rows of non-essential text to be removed, leaving the essential data shown in rows 4,8,9,10 & 11.  Originally, when I was processing a handful of tables I’d bring those tables to PQ and modify the first one to go into proper pivotable format, then copy the M-code to the other, and append everything.  That’s too much cutting/pasting with dozens or more tables so I’m looking to make this easier.
3. There are null rows between the consolidated table data
4. I’ve shown 2 representative samples of data for Cost Centers 1001 & 1002.  There’s at least (18) more not shown in this simple pictorial

Once the data is finessed,  it’s needed in the format shown here:

https://photos.app.goo.gl/oaLQ.....mvsRkDm1Y7

This is a very simple representation.  In theory, if every cost center used all 1000 GL expense accounts with 12 months of budget/actual data for each cost center, the pivotable data would be 20 x 1000 x 12 x 2 = 480,000 rows of data  (!!).  This would be a very large spreadsheet and I can’t imagine the delays in working with it in Excel, hence the need to use PQ to modify it to pivot table form only (connection only).

With the pivot data shown I can add slicers or whatever I need to parse the data.  Also, as more months add to the model I will just add more sheets to the PQ queries page, but the steps needed to append them WITHOUT modifying each one individually first is what I’m after.

Any suggestions would be greatly appreciated.  I also wondered about some form of PQ looping with a standard list of instructions that would iterate through the complete list of tables, but I’ve no idea how to do that.  And VB isn’t an option; I’m a bit green there and would prefer to do everything in Power Query.

Thanks in advance, looking forward to more experienced minds than mine to make some recommendations on this!

Steve