Forum Discussion
Tranform the data
Hi everyone
i have 30 person data in 30 excel files and each file consists of 52 week data sheets
Now i have selected a sample file and did some operations on them like cleaning and all. But i dont know how to make a function that perform sample file working things in all sheets of a file.
Hii Ankitvardhan
TO FIX IT
Step 1: Create the Sheet Transformation Function
- Open your Sample File and connect to one specific sheet.
- Perform all your cleaning steps (Removing rows, renaming headers, etc.).
- In the Queries pane (left side), right-click your sample query and select Create Function. Name it fnTransformSheet.
Step 2: Connect to the Folder
- Go to Get Data > File > Folder.
- Point it to the folder containing your 30 Excel files.
- Click Transform Data (Do NOT click Combine yet).
Step 3: Expand the Sheets
Now, instead of clicking the "Combine" button in the Content column, we will manually expand the sheets so we can apply your function to every single one.
- Click the Add Column tab > Custom Column.
- Use the formula: = Excel.Workbook([Content]). This will create a new column containing "Table" objects of all sheets.
- Click the Expand icon (two arrows) on this new column and select only the Data and Item (Sheet Name) columns.
- Now you have a row for every week (30 files × 52 weeks = 1,560 rows).
Step 4: Invoke your Transformation Function
- Go to the Add Column tab > Invoke Custom Column.
- Select your function fnTransformSheet.
- For the input parameter, select the Data column (which contains the raw sheet content).
- Power Query will now run your "Sample" cleaning steps on all 1,560 sheets simultaneously.
If this "Invoke Function" method successfully cleans all 52 weeks for your 30 people, please mark this as the "Accepted Solution"!
7 Replies
Hi Ankitvardhan,
you can put all those files into a filder and import them all at once doing the same cleaning on all of them
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Here more details on how to proceed ather connecting
https://insightsoftware.com/blog/power-bi-load-data-from-folder/
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi Ankitvardhan ,
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank FBergamaschi , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.Best Regards,
Community Support Team
Hii Ankitvardhan
TO FIX IT
Step 1: Create the Sheet Transformation Function
- Open your Sample File and connect to one specific sheet.
- Perform all your cleaning steps (Removing rows, renaming headers, etc.).
- In the Queries pane (left side), right-click your sample query and select Create Function. Name it fnTransformSheet.
Step 2: Connect to the Folder
- Go to Get Data > File > Folder.
- Point it to the folder containing your 30 Excel files.
- Click Transform Data (Do NOT click Combine yet).
Step 3: Expand the Sheets
Now, instead of clicking the "Combine" button in the Content column, we will manually expand the sheets so we can apply your function to every single one.
- Click the Add Column tab > Custom Column.
- Use the formula: = Excel.Workbook([Content]). This will create a new column containing "Table" objects of all sheets.
- Click the Expand icon (two arrows) on this new column and select only the Data and Item (Sheet Name) columns.
- Now you have a row for every week (30 files × 52 weeks = 1,560 rows).
Step 4: Invoke your Transformation Function
- Go to the Add Column tab > Invoke Custom Column.
- Select your function fnTransformSheet.
- For the input parameter, select the Data column (which contains the raw sheet content).
- Power Query will now run your "Sample" cleaning steps on all 1,560 sheets simultaneously.
If this "Invoke Function" method successfully cleans all 52 weeks for your 30 people, please mark this as the "Accepted Solution"!
Hi Ankitvardhan
Load all 30 Excel files using Folder connector from Get Data and apply cleaning steps in Power Query. Power BI will automatically turn those steps into a function. Please watch this video : https://www.youtube.com/watch?v=WalVo3lpbIw
Hii Ankitvardhan
If this "Invoke Function" method successfully cleans all 52 weeks for your 30 people, please mark this as the "Accepted Solution"!
