Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Cyiin
Frequent Visitor

Combining multiple sheets (but not all) from multiple Excel files in a Sharepoint folder

Hi!

 

I have a Sharepoint folder of monthly reports. Each report has more than 10 sheets, but I only need to get the data from 5 specific sheets (some of the files may not have all of those 5 sheets). The 5 sheets have pretty much the same format but requires some cleaning before the data can be combined with the others (needs some unpivoting first because the number of columns are not the same month on month).

 

I've tried the automatic combine and transform in power query, but it seems like I can only combine one sheet from all files or all sheets from all files.

 

Anyone knows how to do this or can point me towards an existing article/guide? Thanks!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Filter the files you want to expand and click the Expand icon. You should get this:

edhans_1-1664417739492.png

 

Click on the Parameter folder, not any sheet tabs

The next screen will look similar to this:

edhans_2-1664417800622.png

Filter the KIND column to only show Sheet if that is what you want.

Then filter the Name column to only show the 5 that you want. If one workbook only has 4, it won't matter.

Then click the Expand icon by the data column.

 

This will expand all 5 sheets (or as many as is available) in all files, but will ignore the sheets you do not want.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
Cyiin
Frequent Visitor

Thank you, @edhans ! This was exactly the direction I needed.

For my particular case, I used the technique from Chriss Webb's blog to create a custom function to clean the data per sheet before expanding/combining them. I just tweaked it a bit to parameterize the Excel file name in addition to the parameterized worksheet names.

Chris Webb's BI Blog: Combining Data From Multiple Worksheets In The Same Excel Workbook Using Power...

Great @Cyiin Glad I was able to help



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

Filter the files you want to expand and click the Expand icon. You should get this:

edhans_1-1664417739492.png

 

Click on the Parameter folder, not any sheet tabs

The next screen will look similar to this:

edhans_2-1664417800622.png

Filter the KIND column to only show Sheet if that is what you want.

Then filter the Name column to only show the 5 that you want. If one workbook only has 4, it won't matter.

Then click the Expand icon by the data column.

 

This will expand all 5 sheets (or as many as is available) in all files, but will ignore the sheets you do not want.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors