Power Query loading sheets inconsistently

PowerQuery is case sensitive so check headers in every table

"header" and "header " are two different names

or attach any examples (desensitized)

The headers are created by the macro, every sheet is identical in structure. I created new files so that I could ensure that nothing was changed and I get the same results.
Its format the sheet, rename it, add a sheet, format it, rename it, repeat.

These are the columns in the sheet.
Cols0.jpg


These are the columns in power query. As the Case Action changes from the first set of sheets (Case Action Dec 01-thru Dec-30) to the next (Case Action Dec 31 thru Jan 29) everything moves one column to the left.
Cols1.jpg

Ok, I'll try again: attach desensitized example excel files
Pictures are useless

and example result (created manually if needed) what you want to achieve

Hi armlegx,

You have two different looking tables on each "Action" sheet. If this were a text file you could filter them out somehow, but merging Excel sheets you need Tables of Data.
So the rows above 16 on each sheet are pulled into the PQ and confuses the import/merge.

Either create tables of data on each Action sheet starting at row 16 and pull them in as tables or move the top table over to the right.
I'm also concerned about any column heading that is the same in or between tables.

Does this help:
https://www.excelguru.ca/blog/2015/0...n-power-query/

Note how Puls has a single table on each sheet and not two..

You need to revise the result of your VBA or you will have a lot of manual work.
IMHO you can use "from file" then filter to stay with Action tables. Next remove first 15 rows from each table, promote headers, sort tables for correct order then append and add ID column (in this case: Amy).
Your vba produce many blank rows what is totally unnecessary for tables.

Anyway if you do this correctly for all 30 workbooks you need do this only once independently from VBA or changing your Action ranges to tables.

see attached
(path is C:\test\ )

Hi armlegx,

I have a possible fix/solution. Remove the blank column A on each of your Case Action sheets and rework the second half of your script. Without a blank column A the import seems to not shift columns on the second sheet.

I think what is happening is, the programmers never considered two tables on the same sheet. There focus is to merge columns with the same spelling in the column head. You don't have official "Tables of Data" so it simply is treated somewhat like a txt file import. When it gets to the second sheet it is trying to match the column head names and can't find one that is null. It then shifts to the first one that has something in it to try to match. The shift happens on the second sheet.


I wish there was a way/method to delete the first 15 rows on each new sheet that is pulled into the query. I need to study to see if that is possible in M Language.


The other way to do this is to create a "connection only" for each Action sheet individually and then merge them all to one big merge.

First 15 rows and first blank column, so your table should start from A1
but you can do that in PowerQuery. Choose easier way for you

edit:
changing column names should be your last step in PowerQuery
note:
I said : Append not merge - in my previous post