Reply
gancw1
Resolver II
Resolver II
Partially syndicated - Outbound

Help with loading budgeting files

I have an Excel file with a many sheets in the same format.

gancw1_1-1722565988878.png

gancw1_2-1722566032343.png

How do I load and combine the sheets into this format 

gancw1_3-1722566091983.png

 

The sample file is here 

Thanks

 

1 ACCEPTED SOLUTION
gancw1
Resolver II
Resolver II

Syndicated - Outbound

I have managed to get what I wanted by

1. Load all the sheets, 2 in this case, as one base table

gancw1_0-1722843393929.png

2. Create a header table from the base table by retaining only the header rows and pivoting by Dept and Name

gancw1_2-1722843609318.png

3. Create the data table from the base table by filtering off header records and merging the table with the header table above 

gancw1_3-1722843695778.png

 

View solution in original post

8 REPLIES 8
gancw1
Resolver II
Resolver II

Syndicated - Outbound

I have managed to get what I wanted by

1. Load all the sheets, 2 in this case, as one base table

gancw1_0-1722843393929.png

2. Create a header table from the base table by retaining only the header rows and pivoting by Dept and Name

gancw1_2-1722843609318.png

3. Create the data table from the base table by filtering off header records and merging the table with the header table above 

gancw1_3-1722843695778.png

 

v-zhangtin-msft
Community Support
Community Support

Syndicated - Outbound

Hi, @gancw1 

 

I was not able to open your link, but you can try this method.

New table:

 

vzhangtinmsft_0-1722835930669.png

Is this the result you expected?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Syndicated - Outbound

Can you try this link instead

https://docs.google.com/spreadsheets/d/1ccpdrj_thuwmvMCDvm6Kb7yPl_3ttYV4/edit?usp=sharing&ouid=10100...

 

There can be many sheets so I need a way to do this without hardcoding the number sheets

Syndicated - Outbound

Hi, @gancw1 

 

It still won't open.

 

Best Regards

Syndicated - Outbound

I have updated the link. Can you try again

Syndicated - Outbound

Hi, @gancw1 

 

I've tried so many times, but I still can't open it.

 

Best Regards

rajendraongole1
Super User
Super User

Syndicated - Outbound

Hi @gancw1 - you can load and combine multiple sheets with the same format from an Excel file in Power BI.

you can use either append transformation in Power Query editor or all sheets you can combine together and invoke a custom transformation use it while doing always preview your data after each step to ensure it’s correctly loaded and combined.

Please share sample data, if still the process is not clear.

 

Hope it helps

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Syndicated - Outbound

I know how to process and append the tables but not sure how to merge the 2 header fields with the table.

sample file here
https://docs.google.com/spreadsheets/d/1ccpdrj_thuwmvMCDvm6Kb7yPl_3ttYV4/edit?usp=sharing&ouid=10100...

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)