Mutiple workbook with multi work sheet

[ankitvardhan987]

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

[AshokKunwar]

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

1. ​Get Data > Folder and select your folder with the 30 files.
2. ​Click Transform Data (do NOT click Combine).
3. ​Add a Custom Column: = Excel.Workbook([Content]).
4. ​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).
5. ​Now you have a row for every sheet in every file.

​Step 3: Invoke the Function on the "Data" Column

1. ​Go to Add Column > Invoke Custom Function.
2. ​Select your modified function.
3. ​For the input parameter, select the Data column you just expanded.
4. ​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"!

[cengizhanarslan]

If you have many workbooks with similar structure (same sheet names + same columns):

1. Place all the Excel files in a single folder.

2. In Power BI Desktop:

   • Home → Get Data → Folder

   • Browse to the folder.

   • Combine & Transform Data

3. 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.

4. Transform as needed.

One query that automatically includes all sheets from all files, no need to manually handle each workbook.

[tharunkumarRTK · Accepted solution]

@ankitvardhan987

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!

[ankitvardhan987]

Thanks followed the steps and did minimal changes and got the answer

[AshokKunwar]

Hii @ankitvardhan987 

If this allows you to see all 52 weeks of data for all 30 people, please mark this as the "Accepted Solution"!