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!
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
|
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"!
