How to Merge Excel Files with Different Headers in Power Query | List.Accumulate

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

LINK to Recursive LAMBDA video: https://youtu.be/L7s6Dni1dG8

LINK to more Power Query tutorials: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-

★ My Online Excel Courses ► https://www.xelplus.com/courses/

✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/

EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/

Get Microsoft 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593

GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U

More resources on my Amazon page: https://www.amazon.com/shop/leilagharani

Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel #powerquery

Видео How to Merge Excel Files with Different Headers in Power Query | List.Accumulate канала Leila Gharani