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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
lukmtb08
Helper III
Helper III

Power Query Comination of Excel files with different sheets

Dear community, 

I have one structure excel file, which defines sheets and columns to be loaded. 

 

Then from Power BI Dataflows, I created lot´s of queries which combine the files in a folder.
Now, my new files that I need to load, have new excel sheets. Therefore, I would like to load those sheets as well. 

I know, that I need to add those sheets in the structuring file. Do also need to add the new sheet in all the other files? Is there a trick, so that I do not need to add those sheetsin all files?

1 ACCEPTED SOLUTION
rubayatyasmin
Super User
Super User

Hey, @lukmtb08 

 

PQ supports dynamic reading of multiple Excel file. You can use Table.Combine() 

Here is an example,

 

let
Source = Folder.Files("Path to your folder"),
ExcelFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"),
CombineSheets = Table.Combine(
Table.TransformColumns(
ExcelFiles,
{"Content", each Excel.Workbook(_, true), type table}
)[Content]
)
in
CombineSheets

 

Here is an GUI tutorial that might help

 

Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamic...

 

Solved: Combine multiple excel workbook with multiple shee... - Microsoft Fabric Community

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

1 REPLY 1
rubayatyasmin
Super User
Super User

Hey, @lukmtb08 

 

PQ supports dynamic reading of multiple Excel file. You can use Table.Combine() 

Here is an example,

 

let
Source = Folder.Files("Path to your folder"),
ExcelFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"),
CombineSheets = Table.Combine(
Table.TransformColumns(
ExcelFiles,
{"Content", each Excel.Workbook(_, true), type table}
)[Content]
)
in
CombineSheets

 

Here is an GUI tutorial that might help

 

Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamic...

 

Solved: Combine multiple excel workbook with multiple shee... - Microsoft Fabric Community

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.