Sign Up to the Full Course: https://www.xelplus.com/course/excel...

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:    • Excel RECURSIVE Lambda  Create loops...  ).

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/excelp...

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:    • Excel RECURSIVE Lambda  Create loops...  

LINK to more Power Query tutorials:    • Excel Power Query (Get & Transform)  

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

➡ Join this channel to get access to perks:    / @leilagharani  

☕ Get the Official XelPlus MERCH: https://xelplus.creatorspring.com/

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

RESOURCES I recommend: https://www.xelplus.com/resources/

Let’s connect on social:
Instagram:   / lgharani  
LinkedIn:   / xelplus  

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!

#Excel #powerquery