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

Sign Up to the Full Course: 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... ).
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: courses.xelplus.com/p/excel-p...
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...
LINK to more Power Query tutorials: • Excel Power Query (Get...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: 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

[@LeilaGharani]

Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter

[@TheBigpapax30]

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

[@chh8860]

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"

[@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.

[@LeilaGharani]

Excellent! This makes me happy. Thank you.

[@kristenlloyd7774]

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!!!

[@cradleofrelaxation6473]

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.

[@gauravsukhadia638]

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

[@shaunrowley7230]

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

[@kasthuric8023]

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

[@cdyard1]

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.

[@user-qg2nc5py8p]

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

[@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!

[@pk5134]

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

[@goodnewskasparyaodzramedo9097]

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

[@ITTricksScroll]

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

[@21121990jay]

I've been using power query quite a lot but didn't know about this transformation. You're Awesome Leila.🤘

[@LeilaGharani]

Awesome! Thank you!

[@sirrob0t]

By far one of the best (if not the best) excel content creator on youtube. Simply amazing Leila

[@LeilaGharani]

Wow, thank you!

[@elainemattias9465]

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

[@reimievans2529]

I just append all and then merge and rename the columns. If a new column appears on refresh I can go edit the merge step. But I'll try this method soon because I'd rather learn it before I actually NEED it. Then I'll eagerly await the appropriate messy data opportunity.

[@willp5309]

I like watching this video at 2x speed. Easier to understand. 😆 great tip! Thank you for all your do!

[@badteestore]

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

[@TheGreyhound31]

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

[@wayneedmondson1065]

Hi Leila. Nice to see a Power Query topic this week! Great challenge and solution. Thanks for sharing :)) Thumbs up!!

[@LeilaGharani]

Thanks for watching Wayne! It's a bit of a jump from my previous Excel for Beginners video 😉

[@EricHartwigConsulting]

This video is amazing, thank you so much for sharing Leila! I have taken your course on mastering power query but i must have missed the part about List.Accumulate so i will need to go back through the course again but what great idea!

[@LeilaGharani]

Glad you like it. It's included in the Pro section on XelPlus.

[@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! 😊👍

[@LeilaGharani]

Hi Vijay. I fully understand. It took me a few weeks to understand how the list.accumulate function works 🤯

[@ugochukwuonyeka1735]

Leila, I love your honesty. Now I do not feel bad for not catching this explanation the first time. Thank you for all that you do for us and for Microsoft Excel, Leila.

[@zaydarendse2812]

Lovely!! you've just taken a load of stress off my table :) thanks for sharing!!

[@LeilaGharani]

You are so welcome!

[@alankc1937]

Excellent video, this lesson is quiet advanced because it has some functions not easy to explain at first besides List.Accumulate (each, underscore, the record function), but you covered perfectly all this topícs in your Power Query course. If somebody reads this comment, take Leila's course, it's worth and mind blowing.

[@LeilaGharani]

Thank you!

[@ankitlahase1836]

same case as show in this video...only one of my excel file have more than one sheet and .. when i try this it catch only one sheet data.what need to do to catch both sheet data

[@joshuahess2649]

Thank you for the video, it really helped me understand some of the tools available. I'm still not sure how to solve my specific situation though. If you, or anyone reading this, knows how, please let me know! -I receive multiple excel workbooks from different clients every day. -I want to combine them into one sheet in one workbook. -They are NOT formatted as excel tables. -All of them merge and center the first row above their column "headers", and add text, sometimes different text between the clients. -I do not need this first row at all, but it must be there when the client sends it. -I do not want to manually remove the top row from each workbook. -Some of the "headers" match, some are different but mean the same thing, and some are unique and need to be incorporated, with blank spaces for the worksheets that don't have data in that column. -Some worksheets have a merged and color-filled bottom row, sometimes with text, sometimes the bottom two rows are merged (individually, not with each other). -I do not need any of the information in these merged cells. -Two of the columns are "DATE" and "TIME", and I need the entire end product to be sorted in ascending order for both. -The sheet names are always inconsistent.

[@johnborg5419]

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

[@naimapedrono9665]

Thank you Leila. Vert useful for me. I need your help : what’s the equivalent to “if nested” in power query? I generated a great ledger and would like to sort in a column, every operation by its nature to present the accounts to my end users who are not very familiar with the accounting vocabulary.

[@fernandacastillo6133]

This was so insightful! Loving your videos! I'm new to PW and I've run into another problem with a new data set generated from a site I use to track orders. It generates an excel file where One row has multiple columns with the information I need and there's no specific distance between columns that repeat that information although the columns all have the same header. Any ideas on how could I merge all those repeated headers into one column for every item?

[@sachinrv1]

Wonderful video Leila. It's difficult to digest. One really need to practice this functions many times before getting a dab hand on it. Thanks. Can you also do a video on excel 365 solution to this different header problem?

[@mariaalcala5159]

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

[@rajat13777]

Love your videos. Simple to follow & real scenarios. So if I have a text/ csv file, downloaded from the web (with garbage data as well) and I want output in a certain format in excel, what would be better - Power Query or Macro? Thxs

[@danielvolgan]

Hi Leila, thank you for the very useful videos. I have a question. Is it possible to, instead of combining and transforming multiple files in a folder, transform first then combine the resulting tables? How to do it? Thanks.

[@sumitrathod2814]

Hi Leila your vedio most useful to us....!!!! Keep updating more vedio of power query...!!!! Thank you so much for Dil se...... ❤️❤️❤️❤️❤️❤️

[@alanlamb6047]

Thank you for another great video I was curious to see if I could create a simpler approach for this specific problem. So, in the interests of sharing... Rather than the very useful but complex formula at 12:30 = Table.TransformColumnNames(TSalary_Table, each List.Accumulate(Table.ToRecords(Mapping) ... etc etc etc Suggested formula = Table.RenameColumns(TSalary_Table, List.Zip({Mapping[Before],Mapping[After]}), MissingField.Ignore) Translation The second parameter of Table.RenameColumns is a list of lists, for example {{"NOMBRE", "Name"}, {"DEPARTMENTO", "Department"}, {"SALARIO", "Salary"}} So, how to turn the mapping table into a list of lists Any of these options will do the job List.Zip({Mapping[Before],Mapping[After]}), or Table.ToRows(Table.SelectColumns(Mapping,{"Before", "After"})), or Table.ToRows(Mapping) Add the extra parameter MissingField.Ignore to ignore issues with attempting to rename columns that don't exist in specific files

[@enl3299]

Very elegant, thanks for sharing!

[@pointsofinterest4300]

I couldn't get the solution from the video to work for my use case, but your did. Thanks for sharing!

[@henrytaverner1803]

Excellento!

[@reng7777]

MY FRIEND YOU HAVEW SAVED MY LIFE!! HEADS UP TO YOU FOR YOUR FORMULA SUGGGESTED TO LEILA, THIS ONE DID WORK FOR ME.!! BEST REGARDS!!

[@chandankumar-np9ez]

Your videos are very helpful, thank you. Please make a video explaining how to use this technique before expanding the table.

[@mattschoular8844]

There is some very useful information in this video. I will need to watch it again for sure. Thanks Leila

[@LeilaGharani]

Glad it was helpful!

[@DaleQuest]

This is great if all of your data is in separate files, but I'm having trouble making this work with nested tables. I'd built a template before this video, sadly, so have used a recursive custom PQ function. Is there any advice out there to make this work?

[@sanjeevsoni6793]

It works, But is going overhead.. Three Cheers to Leila, Well expalined, Many Thanks for sharing the knowledge

[@LeilaGharani]

You're welcome!

[@paser2]

Brilliant use of power query. Thank you!

[@ebrusalic4342]

I wish I could give thousands of thumbs up. I love it and will use it a lot

[@magylopez2308]

Gracias por tus excelentes videos Leila!!

[@lesportautrement]

Excellent and strong ! One question : how activate the formula suggestion in PQ ??

[@warrentso2548]

Thank you for sharing! I have followed every step but it still not working in my case, i am not sure if it is because my data file is not a table. There are too many files that i cannot set them to table format one by one.

[@juja2819]

Thank you! :) But would it work if there were different number of columns in some tabels regardless of language?

[@ExcelWithChris]

Still not right there with my knowledge, but going to save and watch when needed. Thanks for another brilliant one.!!

[@LeilaGharani]

Thanks Chris. This one does take a while to digest!

[@kethanchordia]

Woah.. this is like magic.. thanks for the wonderful video on our daily battle with inconsistent data.. really helpful 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻

[@LeilaGharani]

My pleasure 😊

[@USAMark70]

Thank you for this! I have multiple tabs (worksheets) within a single workbook. I'm getting confused because it adds a "Promoted Headers" step. Anyone know how to handle that?

[@dagmarab727]

You are amazing! Thank you for all your videos. Taking the chance, do you know how I can import all tables from multiple pdf files? I have a multiple pdf files and each file contain different amount of tables...

[@tapio_m6861]

This is great! Thank you a ton, Leila!

[@Texmex85035]

Leila, would assume that this function would also work if I were to have same line items but in different cell ranges from multiple worksheets?

[@RajaMaja-zk3dz]

exactly what I'm looking for, thanks again Leila

[@pracashraz007]

Awesome Leila, with this session, I have become a big fan of you 😘😘😘😘 Like this can you make an another session for matching data also please

[@Kivarenn82]

"we've asked the companies to fix their data. But they don't listen" Heard.

[@ZakNazif]

Great presentation, as usual! How to append from several tables (not files) loaded in my workbook with different column headings and numbers? Very much appreciate your response. Good Luck!

[@vivekphadke16]

Excellent, will have to create dummy files practice and understand. Thanks a lot, Leila

[@superlaffin39]

Hi Leila, What if you have a mapping sheet for header reference, the AFTER table is in column A and the BEFORE is in column B and C and D?

[@vipulagarwal5155]

hell of a video.. blown my mind into pieces... damn I'm trying this one to grab a hand on... Thanks Leila.. Love u lots..

[@Zugey1983]

Me sirvio demasiado este video para integrar mis tablas, mil gracias!!

[@SyedMuzammilMahasanShahi]

Thank you so much for this wonderful video. It's gonna help us a lot.

[@samtrupt]

Amazing video, need to ask you question about slightly different issues not sure how to compute

[@schrevan]

Is it possible to manipulate data tables with headers and sub headers? For example let’s say you have a list of 10 items in column A. Then columns B and C are sub headers for units sold and $ sold, respectively, for Store #1, the header. Then you have columns D and E as units sold and $ sold (sub headers) for Store #2 (header), and repeat for as many stores as you have. Is it possible to use this type of data table

[@smithanimal]

Power Query is a new animal I've been diving into at work lately. This is fascinating. I am already amazed at what you can do with just excel but this really has me ready to dive into the course and learn Power Query. Is Power Pivot similar to this or is that a different process all together?

[@alessandratorsello9511]

Dear Leila I love your video tutorials, Although, this time, I don't seem to understand the basis of your video. I understood that to create a mapping table I must first manually list the headers ("BEFORE" column) of all the tables I am working on and then create a second column ("AFTER") with the names I want to transform the "Before" Headers column into. How can I handle this procedure if my tables are many? Won't I waste too much time collecting all the headers? What did I miss/not understand about your instructions? Thank you if you may reply

[@mduan314]

This is very complicated formula to transfer the name format. I hope there is a better and simplier method to fix the name format difference. Thank you!

[@akshaymane7697]

Hi.. Thank for your efforts in this. Could you also suggest easier way of combining data with different headers ?

[@thaotv]

Thanks for very usefull solution. I have two querries with different headers loaded from website, are there any way to append its?

[@nunofaia]

Amazing Explanation Leila , i tried to reproduce the above example and it worked :) with a slight hiccup between the Column1 and Column11 as my before is just a list of Columns with a number (Column1, Column2 etc) for some reason the Current and Sate in the text.replace function recognizes Column11 as Column1 and therefore applies the after of Column1 instead of Column11 , is there a way to force the Text.Replace to look for the exact string and not an approximate :) ? Much obliged once again for the Amazing Video

[@minhvuongluu7644]

How about table with different columns ( for example like new SKU each month we have to add 1 column ), and I need to unpivot data from multiple excel files. any video you would suggest?

[@algh8699]

Hi, can you create a video of how we can link dynamically so the link never changes. Lets say in jan folder, we have wkbk1 that has links to wkbk2 and wkbk3 and then when we copy them to feb folder we need to relink them, is there any way that not to relink? Thanks

[@rabbitwang214]

It is a excellent and hard lesson!!! Would you mind share your example files for us to practice? Many thanks

[@grisheldamarks-webster-noa1706]

This is a different subject matter but your assistance will be greatly appreciated. I know how to set the scroll area so I won't fly down to row 1245. How do I save these changes. Whenever I close the workbook, the scroll range is deleted. I have saved it as a macro enabled workbook and that did not work. Thank you

[@mdexcelpro8495]

I am very excited really your teaching skills are another level

[@LeilaGharani]

So nice of you

[@fabricebourgeat8491]

Thanks Leila - as always great explanations (but it will take me time to "digest" this one...). Now you mentioned one thing at the very end about "buffer your mapping to boost the performance". What do you mean? I have start to use PwrQ 2-3 months ago, importing various files, apply all types of mapping (not on headers as I did not know how to do it so far...) but I am facing some random performance issues. I am a bit lost between when we should load the data in the data model, or only do a connection, or just load the data... Can you explain why it seems that some times it is super fast and sometimes it takes ages? Would the "buffering" you suggest help? Or may be you have already done a tutorial on this topic... Thanks in advance!

[@nitigyahanda3516]

Hi, thank you very much for this video! I have a follow up question. What if the other files have different number of columns and values in different formats? For example in my case, "May- 2024 Sales" file contains all columns from previous months but also additional columns that were added to it and wouldn't be present in say "Apr-2024 Sales" file. Ideally I want all older files to display Null in this new column but I am not sure how to do it. I've been stuck on this problem for a while, I'll really appreciate it if you can help me out.

[@Sierratana]

Fantastic!! Just what I needed! You are clearly a genius! :)

[@LeilaGharani]

Glad it helped!

[@SNOOPYDOG1976]

Good evening, I'd like to ask 2 questions: 1) once we get the Power Query processing in relation to the settings we created and create a "table", is there any way to automatically export it as an XLXS file so that it is no longer related to the Query? 2) Related topic: if we have a file consisting of multiple columns, can we provide with a "command" the saving of as many files as there are "row" fields of a given column? Thanks in advance

[@sriratna9211]

Hi, thats a good solution! But, what if I'm talkin about a sheet that contains like 300 hundreds lines and 30 columns, and I need to get a specific line x column, and the input of this data needs to be on a specific line x column too, is this possible?

[@mamadoubah3686]

Great video! Thank you!

[@somasundarammohan6202]

Thanks a lot, you have saved me 8 hrs of work that i do on a quarterly basis.

[@LeilaGharani]

Glad it helped!

[@somasundarammohan6202]

@@LeilaGharani I tried and I found something wrong with it. My two sources have "POLICY" and "POLICY_NO" both mapped to "POLICY NO". Query reflects only data from "POLICY_NO" and column head is changed to "POLICY NO NO". Could you please help on this or suggest where I can learn from?

[@MariaRamirez-zd3jy]

Is there something available instead of Header horizontal I can do the same function vertically??

[@SavageGothamChess]

Thank you so much Leila for the video. Well explained

[@LeilaGharani]

You're welcome 😊

[@Galileo2pi]

Pretty useful, as always excellent, thank you Leila

[@LeilaGharani]

Glad you think so!

[@ryano4515]

Question: would this work for columns with no headers. Right now power query adds headers in for me (e.g. column1, column2, etc). Would I be able to use this (column1) for mapping?

[@antoineferrand149]

Hi! I’ve found an more direct function which is list.zip() ! It needs only {reftoconversion_table[before], reftoconversion_table[after]} and the final argument for missing elements is also very nice! Thank you Leila 😉

[@LeilaGharani]

Thanks for sharing, Antoine!

[@ankitlahase1836]

same case as show in this video...only one of my excel file have more than one sheet and .. when i try this it catch only one sheet data .what need to do to catch both sheet data?

[@MabenitoIII]

This is going to save me 5 millions months of arranging data files from our regions. Plus I need not to work my system generated report any longer. Just download into a folder and that's it.

[@Jocedu06]

This is awesomely explained, great one!

[@LeilaGharani]

Glad you liked it!

[@patrickkinbonso1809]

Hi Leila, I hope all is well. Do you know how to use Power Query to merge files from a folder where the data has no column name / header please?

[@mr.gk5]

Could you elaborate more on buffering mapping table? What do you mean exactly? I'm confused

[@audreyhuang7143]

Where to find the excel files you are using in order to follow along? Thanks.

[@sherryfox9688]

Would love for you to provide a copy of this workbook. I am still having issues making this one work. Thanks!

[@mirrrvelll5164]

It is a very useful thing but in a case when you have "almost perfect" environment of data, or very close to that. But reality is of course different, where you are dealing with a jungle of data, which gives hard time to transform properly.

[@denconstutube]

Way past my skill-set or need, but great information and interesting to watch a master at work.

[@garylillich]

Do not feel alone. Sometimes, we use brute force to get a similar task done.

[@mahathmasadineni2884]

Hi Leela grani in power query is there any option is available to get the data of duplicates in a single row.

[@shyamadasgupta9423]

Hi Leila, wonderful video. Can you tell me when to use List.transform and when to use list.accumulate and if one is superior to the other. Thank you.

[@Pinkweirdo00]

hi, for this scenario, power query will only look into the first sheet tab of an excel file right? it will not look into the other sheet tabs?

[@babuasian]

When i'm replacing headers(using List.Accumulate), it is replacing the exact text and leaving the rest of the text as is. What do you suggest Leila..?

[@rajeevgosavi4514]

Very well explained. However, we would like to follow your tutorial step by step, as you have explained that may be easier if you can provide a link to download excel files under reference. Thanks.

[@joefromdc]

HI, Is there a way to change Get Data (File) to Get Data (Folder) within the power query process