Forum Discussion
Mutiple workbook with multi work sheet
Since you want to perform same transformation logic to all the sheets and also in all the excel workbooks. Assuming the data structure across all files and all sheets is same. I would suggest to follow the below approach, instead of using the default UI options
1. I took three excel workbooks, each contains 3 sheets
2. I imported them into power bi, Dont click on "combine", click on "Transform"
4. Delete all the columns, except "Content" and "Name" (Unless you need those other columns)
5. Using the Excel.Workbook function, converts the binary column to table
6. Expand "Content" column, this will bring all the sheets from each file
Optionally you can merge the file name and sheet name if you want
7. Now create a power query function for all your transformations, like this. (Please observe, in the function accepts the "Table" as input not the file. It takes table of data as input and performs the transformations)
8. Now apply the function on the table column
As you can see transformations are applied
9. Now you can expand the "Data" Column to get the full table
Thats it!
power bi file and sample excels are attached
You can read my blogs here: https://techietips.co.in
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
If you have many workbooks with similar structure (same sheet names + same columns):
Place all the Excel files in a single folder.
In Power BI Desktop:
Home → Get Data → Folder
Browse to the folder.
Combine & Transform Data
In the Combine Files window:
Choose the sheet/tab you want to import from each file (e.g., “Data”).
Power BI will generate a query that reads that sheet from every file in the folder.
Transform as needed.
One query that automatically includes all sheets from all files, no need to manually handle each workbook.