Combing few excel workbooks from one file with data transposition in one shot

[MKPartner]

I have one excel file on sharepoint with multiple workbooks named as year. I'd like to load the data into Power Bi to prepare reports. The issue is the all workbooks have a data in row so I need to transposition them. Next woorkbooks will come with the next years. 

When I'm doing this in one shot, I have only oldest workbook and no data for next year as they were switched into different columns that Date column. 

Is there any method to do it in a one shot in Power Query or I need to firstly transpostioning data before loading data into Power Query ?  

Thank you for support. 

[v-ssriganesh]

Hi @MKPartner,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @MKPartner,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh · Accepted solution]

Hi @MKPartner,
Thanks for posting your query in Microsoft fabric community forum.

I have reproduced your scenario and found a solution that allows you to combine multiple Excel workbooks (sheets named by year) and transpose the data in Power Query in one step.

Steps I Followed to Achieve This:

• Loaded the Excel File into Power BI from my local system (you can do the same from SharePoint).

Transformed Each Sheet:

• Used the first row as headers
• Transposed the data to convert weeks into a column

• Reused the first row as headers to organize the data correctly. Combined all sheets into a single table and cleaned the data to achieve the final format with columns: Year, Quarter, Month, Weekly, Week, Value.

I have attached a screenshot and the .PBIX file for your reference.

Please let me know if you require any further clarifications. If this information is helpful, kindly accept it as a solution and give a "Kudos" to make it easier for other members to find. 

Thank you.

[v-ssriganesh]

Hi @MKPartner,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[lbendlin]

when you say workbooks do you mean "Sheets"  ?

Can you post a sample file ?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...