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.
Hi Mohamed,
When you get data from a folder containing files that need cleaning before appending, you do the transformations in the 'Transform Sample file....' query in the query editor. It then applies those same transformations to every file before appending them. See lesson 2.05 of the Power Query course where I explain this.
Mynda
