- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Help with loading budgeting files
I have an Excel file with a many sheets in the same format.
How do I load and combine the sheets into this format
The sample file is here
Thanks
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I have managed to get what I wanted by
1. Load all the sheets, 2 in this case, as one base table
2. Create a header table from the base table by retaining only the header rows and pivoting by Dept and Name
3. Create the data table from the base table by filtering off header records and merging the table with the header table above
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I have managed to get what I wanted by
1. Load all the sheets, 2 in this case, as one base table
2. Create a header table from the base table by retaining only the header rows and pivoting by Dept and Name
3. Create the data table from the base table by filtering off header records and merging the table with the header table above
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi, @gancw1
I was not able to open your link, but you can try this method.
New table:
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Can you try this link instead
There can be many sheets so I need a way to do this without hardcoding the number sheets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I have updated the link. Can you try again
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
Proud to be a Super User! | |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
07-12-2024 10:19 AM | |||
01-17-2025 01:56 AM | |||
06-20-2020 01:14 AM | |||
12-12-2024 07:20 PM | |||
07-27-2022 06:36 AM |
User | Count |
---|---|
141 | |
115 | |
84 | |
63 | |
48 |