cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Atanas
Helper IV
Helper IV

Combining Multiple Files From Folder With Multiple Sheets

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:

 

Atanas_0-1610033989526.png

 

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:

Atanas_1-1610034274495.png

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:

Atanas_3-1610034605932.png

 

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:

Atanas_4-1610034653408.png

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:

Atanas_10-1610035034798.png

 

Atanas_6-1610034799491.png

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:

Atanas_8-1610034949067.png

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)

Atanas_9-1610034998390.png

Can anyone help me resolve this?

 

All due respect,

 

Atanas

 

 

 

 

1 ACCEPTED SOLUTION
ToddChitt
Post Prodigy
Post Prodigy

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.

View solution in original post

4 REPLIES 4
ToddChitt
Post Prodigy
Post Prodigy

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.

View solution in original post

@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
Post Prodigy
Post Prodigy

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.

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.