Combining Multiple Files From Folder With Multiple Sheets
Hope my message finds you well and healthy!
I would like to combine multiple files from folder where each file contains multiple sheets like so:
At the end I would like to append the 3 of them and clean the data before I upload it to my Data Model.
What I did is I went to the query editor and clicked: Get Data->From File-> From Folder.
I started with only 1 file at the time (I have to refresh this report monthly, so more files to come) and browsed to my folder.
Instead of choosing any of the sheets, i clicked on the actual folder like so:
That took me to the query editor, where i cleaned all the sheets one by one and appended them at the end. All that done in the "Transform Sample File (2) query like shown below:
Needless to say it worked like charm for all the files which i put later. All of them were processed in the same way with all the applied steps. Here is a screenshot of the actual table which i use in my data model:
My problem now is that since January i have the same file which i will put in my folder, but this time i have a new tab for ST SEL United States, which Power Query does not add to my sheets:
For some reason I am not able to see it. I went to the source step and refreshed the query, but there are only the 3 regions which i have so far:
I assume this has something to do with my sample file which is the one from the previous month (and doesn't have this new tab)