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!
Hii ankitvardhan987
Instead of relying on the "Combine Files" helper queries that Power BI automatically generates, you should manually expand the sheets to ensure the function reaches every week.
Step 1: Modify your "Transform File" Function
Your current M-code likely has a navigation step that looks like Source{0}[Data] or uses a specific sheet name. You need to remove the part that selects the first sheet inside the function so that the function can accept any table as input.
Change your function code to this pattern:
(SheetData as table) =>
let
-- Start directly with your cleaning steps using SheetData as the source
#"Removed Top Rows" = Table.Skip(SheetData, 2),
#"Kept First Rows" = Table.FirstN(#"Removed Top Rows", 18),
-- ... (rest of your existing cleaning steps)
in
#"Removed Columns1"
Step 2: Connect to the Folder and Expand Sheets
- Get Data > Folder and select your folder with the 30 files.
- Click Transform Data (do NOT click Combine).
- Add a Custom Column: = Excel.Workbook([Content]).
- Expand that new column to show the Data column (this contains the raw tables for all 52 weeks) and the Name column (the sheet name).
- Now you have a row for every sheet in every file.
Step 3: Invoke the Function on the "Data" Column
- Go to Add Column > Invoke Custom Function.
- Select your modified function.
- For the input parameter, select the Data column you just expanded.
- Finally, expand the resulting column to see your cleaned data from all sheets.
If this allows you to see all 52 weeks of data for all 30 people, please mark this as the "Accepted Solution"!