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.

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

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

🚩Let’s connect on social:

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

[TheBigpapax]

Ok. I was completely LOST. God Bless your advanced IT skill

[PeteMackay]

OMG thank you! I I was struggling with this just last night and just abandoned my effort. I manually changed column headers in my data to get the append working. You are amazing, Excel is amazing and PQ is amazing! Your videos are doing a lot to foster people's love of excel so thank you for making them.

[cdyard]

As usual, a great video and great explanations. It'll take me time, though, to fully understand. But, even if I'm also working with your Udemy courses, these videos are a great help.

[GeorgeKlucsarits]

Great example. Reminded me to go back to the course content and re-watch now that I have a very similar use case. Thanks!

[kasthuric]

I'm happy that I found you ... thanks for sharing your knowledge... means a lot for me for doing my fundamental analysis...your doing really a great work ...

[ITTricksScroll]

What a tutorial!!! Your videos are very effective and efficient for the MS users!!

[pk]

Hi Leila, your tutorials are amazing ! Thank you

Could you please do a tutorial on the underscore operator and the "each" keyword? I find these, especially the underscore, very confusing as I can't figure out which one to use. Thanks

[shaunrowley]

This is brilliant I shall be considering the Power Query Course to purchase at the end of the month with using some of my salary payment - thanks again

[qgncpyp]

This example is very well explained and quite insightful. I'd like to highlight one issue when utilizing the Text.Replace function. The outcomes might be unexpected, as it could inadvertently replace segments of text within a specific column. This complexity increases further when dealing with source files that aren't in the .xlsx format, such as .CSV files

[TheGreyhound]

You are simply awesome... You make such complicated things look so simple... Thanks a lot for your videos🙏

[goodnewskasparyaodzramedo]

This is a big challenge for me. Thanks for the tutorial. It's given me a good headstart at resolving this challenge of combining data of same sizes but different field labels

[vijayarjunwadkar]

Hmmm, honestly this video is little overhead for me first time, so going to watch again, pausing and revising and I am sure I will get it! 😁But many thanks for this useful video, as always! 😊👍

[chh]

1st Question - "Where did you learn your Excel skills?"
Response - "From LGU"
2nd Question - Where?
Response - "Leila Gharani University"
3rd Question - "Is it hard?"
Response - "Yes ... very hard ... but the instructor is great"

[gauravsukhadia]

Concept of mapping is brilliant because people send all sorts of messed up data and to perform ETL operations this is game changer.

[cradleofrelaxation]

Ok!
The most complicated formula I’ve ever seen in excel.
I thought I was done with excel and DAX!

Meanwhile this also depends on the fact that the column names not matching must be consistent and meaningful!

Unlike the scattered column names my colleagues used to send to me.

Well done Ma’am.

[badteestore]

Thank you for your videos! I have learned so much! Keep them coming!

[kristenlloyd]

I’m desperately trying to teach myself excel in a night to get an amazing job- it’s a nightmare and you’re keeping me going right now. Thank you for your videos!!!

[elainemattias]

Hi Leila, I love all the content from your channel and also how didactically your explain everything. :)
I started to use Power Query recently and this issue of having different headers hunted me from the beginning. To solve that I adopted a different approach though, from the one you explained in this video. I load the files with different language only as connections, duplicate the columns, and rename them to the English terms. Afterwards, I am able to append all the sheets. I think that the 2 approaches work well. The choice for the right method is probably personnel and depends on how many columns, how many sheets, and user familiarity with Power Query.

Elaine

[mariaalcala]

I learn a lot from you! Great trick to handle format issues

[johnborg]

Thanks Leila. I need to watch this video many times to absorb :)