cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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!

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!

Proud to be a Super User!





Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors