Consolidating data from multiple Excel files requires you to have identical table headers otherwise you can run into the missing data problem. With this Power Query Advanced tutorial you learn how to handle a complex situation where you need to combine data from multiple Excel files but it’s not easy to match the columns.
The Excel tables in the files have headers that don’t match and they also have a different order. We cannot depend on matching table header names or matching column order to combine the data from multiple Excel files.

In this Excel Power Query tutorial I start by importing data from a folder (Get & Transform - From File - From Folder). To make sure I don’t have missing data, I use a mapping table to map the table headers of the different files to common header labels. This automates the process of appending data.

For the table header mapping, I use the power query List.Accumulate M function. With this function you can loop through the mapping table’s “before” column and if there is a match to replace the header with the label in the “after” column. The great thing about List.Accumulate is you can do multiple word replacements in one function (similar to the recursive lambda video here: https://youtu.be/L7s6Dni1dG8).

This tutorial introduces you to List.Accumulate but if you’d like to really learn it check out my complete Power Query course:

★★★ Get the complete Power Query Course: https://courses.xelplus.com/p/excel-power-query

Time Stamps

• 00:00 How to Use a Mapping Table in Excel Power Query
• 02:47 How to Use Mapping Table for Headers in Power Query
• 07:27 How to Use List.Accumulate to Map Headers in Power Query

#excel #developer