Forum Discussion
Mutiple workbook with multi work sheet
Hi i have 30 people excel file and in each file i have 52 week sheets in which the format is same but ya data is different. Now i have selected a sheet and did the cleaup there. But now i want that the things i did with one file with one sheet will happen to other sheets as well. And then to other file with their sheets too. I have tried but it always show me first sheet data for all files. First sheet data
transform of sample file for first sheet
I have made a function but it always show first sheet but i want that first it transform first sheet and then second and so on
You can see output
its repeated first sheet data.
Note:- Its also giving second file but again first sheet of that file data
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!
5 Replies
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!
Thanks followed the steps and did minimal changes and got the answer
Hii ankitvardhan987
If this allows you to see all 52 weeks of data for all 30 people, please mark this as the "Accepted Solution"!
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.
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"!
