Combining Multiple Files From Folder With Multiple Sheets

[Anonymous]

Dear Community,

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)

Can anyone help me resolve this?

All due respect,

Atanas

[ToddChitt · Accepted solution]

Get Data from Multiple Excel Files with Different Sheet Names into Power BI - RADACAD

You could have gone to the site and scrolled through the articles just as easily as I did.

[Anonymous]

@ToddChitt 

Thank you for your help! I would like to zpologize for the late reply, but I have been pretty busy the last days.

All due respect,

Atanas

[ToddChitt]

RADACAD has a post about getting data from multiple sheets in Excel. 

Power BI Archives - RADACAD

I think you should be able to combine that with getting data from FOLDERS as well, so it includes ALL sheets of EVERY file.

[Anonymous]

Thanks, can you pull the actual link for the article?