Consolidating Multiple Excel Files Using Power Query

Copper Contributor

Hi There, 

I am trying to consolidate multiple excel files into one master template using Power Query. The data is prepared in a table format and headings are aligned. In nature, the data is a budget from different departments/areas which requires to be consolidated without the manual process of copy-pasting. 

 

The challenge here is, that the data is has chapters and sub-chapters and it cannot be easily appended as we need to certain chapters to be combined. For example, the employee chapters from all the 3 area hubs need to be combined into a single heading called 'Employee cost'. The same applies to other headings. 

Most examples I have seen on Youtube are using simple tables that are easy to combine. I wonder if there is a certain way to combine data of different excel files with numerous headings and sub-headings with quite a number of blank spaces, yet once combined we need the format and master excel layout to remain the same.

 

Attached are the 3 excel files I am trying to append/combine. Your swift support is highly appreciated. 

 

Best,

Mohamed

4 Replies
I couldn't attach the files as the attachment option isn't available. Please help!

@Nasir1315 

You may share on OneDrive, Google Sheets, what ever, and share the link

Hi Sergei,
Thank you for your attention to this. Just wanted to confirm that I figured out this and the issue is now resolved.
Thanks,
Mohamed

@Nasir1315 

Mohamed, great to know you sorted this out, thank you for the update