The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to Solution.
Filter the files you want to expand and click the Expand icon. You should get this:
Click on the Parameter folder, not any sheet tabs
The next screen will look similar to this:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingFilter the files you want to expand and click the Expand icon. You should get this:
Click on the Parameter folder, not any sheet tabs
The next screen will look similar to this:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingJoin the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.