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

Video Description

Sign Up to the Full Course: https://www.xelplus.com/course/excel-power-query/ 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 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/ ➡️ Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join 👕☕ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/ 🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/ 🎥 RESOURCES I recommend: https://www.xelplus.com/resources/ 🚩Let’s connect on social: Instagram: https://www.instagram.com/lgharani LinkedIn: https://www.linkedin.com/company/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