Consolidating Multiple Excel Files Using Power Query

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




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


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.


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