How to Merge Excel Files with Different Headers in Power Query | List.Accumulate
HTML-код
- Опубликовано: 19 июн 2024
- 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
Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter
Ok. I was completely LOST. God Bless your advanced IT skill
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"
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.
Excellent! This makes me happy. Thank you.
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.
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
Very elegant, thanks for sharing!
I couldn't get the solution from the video to work for my use case, but your did. Thanks for sharing!
Excellento!
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!!
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 ...
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.
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
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!!!
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
What a tutorial!!! Your videos are very effective and efficient for the MS users!!
Great example. Reminded me to go back to the course content and re-watch now that I have a very similar use case. Thanks!
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
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! 😊👍
Hi Vijay. I fully understand. It took me a few weeks to understand how the list.accumulate function works 🤯
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.
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
You are simply awesome... You make such complicated things look so simple... Thanks a lot for your videos🙏
Thank you for your videos! I have learned so much! Keep them coming!
Concept of mapping is brilliant because people send all sorts of messed up data and to perform ETL operations this is game changer.
I've been using power query quite a lot but didn't know about this transformation. You're Awesome Leila.🤘
Awesome! Thank you!
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
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.
Brilliant use of power query. Thank you!
I learn a lot from you! Great trick to handle format issues
Thanks Leila. I need to watch this video many times to absorb :)
Me sirvio demasiado este video para integrar mis tablas, mil gracias!!
Gracias por tus excelentes videos Leila!!
By far one of the best (if not the best) excel content creator on youtube. Simply amazing Leila
Wow, thank you!
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!
Glad you like it. It's included in the Pro section on XelPlus.
This is great! Thank you a ton, Leila!
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?
Thank you so much for this wonderful video. It's gonna help us a lot.
Lovely!! you've just taken a load of stress off my table :) thanks for sharing!!
You are so welcome!
exactly what I'm looking for, thanks again Leila
Great video! Thank you!
There is some very useful information in this video. I will need to watch it again for sure. Thanks Leila
Glad it was helpful!
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?
Hi Leila your vedio most useful to us....!!!! Keep updating more vedio of power query...!!!! Thank you so much for Dil se...... ❤️❤️❤️❤️❤️❤️
Excellent, will have to create dummy files practice and understand. Thanks a lot, Leila
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
Woah.. this is like magic.. thanks for the wonderful video on our daily battle with inconsistent data.. really helpful 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻
My pleasure 😊
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?
I like watching this video at 2x speed. Easier to understand. 😆 great tip! Thank you for all your do!
It works, But is going overhead.. Three Cheers to Leila, Well expalined, Many Thanks for sharing the knowledge
You're welcome!
Your videos are very helpful, thank you.
Please make a video explaining how to use this technique before expanding the table.
Hi Leila. Nice to see a Power Query topic this week! Great challenge and solution. Thanks for sharing :)) Thumbs up!!
Thanks for watching Wayne! It's a bit of a jump from my previous Excel for Beginners video 😉
Thank you so much Leila for the video. Well explained
You're welcome 😊
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.
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
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.
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.
Pretty useful, as always excellent, thank you Leila
Glad you think so!
Still not right there with my knowledge, but going to save and watch when needed. Thanks for another brilliant one.!!
Thanks Chris. This one does take a while to digest!
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...
Fantastic!! Just what I needed! You are clearly a genius! :)
Glad it helped!
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..
This is awesomely explained, great one!
Glad you liked it!
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!
I wish I could give thousands of thumbs up. I love it and will use it a lot
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.
Thank you!
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
Nice lesson and very useful
It is a excellent and hard lesson!!! Would you mind share your example files for us to practice? Many thanks
Hi.. Thank for your efforts in this. Could you also suggest easier way of combining data with different headers ?
Amazing video, need to ask you question about slightly different issues not sure how to compute
Thanks a lot!
I am very excited really your teaching skills are another level
So nice of you
Thank you! :) But would it work if there were different number of columns in some tabels regardless of language?
Way past my skill-set or need, but great information and interesting to watch a master at work.
Do not feel alone. Sometimes, we use brute force to get a similar task done.
Random question, but how do you make your videos so clean with visuals and transitions. Do you use premiere pro :)
Thanks for noticing 😊 Mostly we use Camtasia with a bit of Premiere Pro and After Effects.
@@LeilaGharani Thanks for letting me know. I’ll have to try and improve my skills with those awesome tools. :)
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!
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
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?
Thanks for very usefull solution. I have two querries with different headers loaded from website, are there any way to append its?
Excellent and strong !
One question : how activate the formula suggestion in PQ ??
Great!!! thank you!
Welcome!
TY :-) Leila, great explanation
You're so welcome!
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
Thanks a lot, you have saved me 8 hrs of work that i do on a quarterly basis.
Glad it helped!
@@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?
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 😉
Thanks for sharing, Antoine!
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?
Great video many thanks Leila
You’re most welcome 😊
Thank you professor Gharani 🤗
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?
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.
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.
"we've asked the companies to fix their data. But they don't listen"
Heard.
This is impressive, but honestly I feel like a macro could accomplish this with less effort. Maybe it's a lazy way of thinking from my end, but it's not just powerquery that is used but some very advanced pq functions. Maybe I just need to throw myself into work that is best done in powerquery so it becomes more second nature to me and then I can iterate to something as advanced as this but this just seems so complex. Definitely impressive, but seems a bit of over engineering. Thank you for opening our eyes to the options out there though.
Thanks for inspiring videos. I was stuggling to get my head around similar kind of problem. After some trial and error found alternative option to reach similar outcome with merge function. On the "Transform Sample File", when the initial table is open, first transpose whole table. Now the original column headers are in Column1. Then the Column1 is merged with file that contains mapping information (columns OldName and NewName). After merging NewName is expanded to visible and moved to lefthand side to first column. Then Column1 is deleted, table is transposed back to original layout and headers promoted. Then just append all files based on the sample file :)
Thanks - your approach is simpler nut Kudos to Leila on the List.Accumulate function approach
Excellent Leila
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
This seemed to be perfect for what I was looking for...multiple spreadsheets with inconsistent columns. Unfortunately I was not able to repeat the functionality in the spreadsheets I have. For starters, when importing the data, it is not finding the table I defined as a parameter. That is TSalary in your example. In addition, when I import the actual data, Excel is ignoring the header in the source document and creating new header names, Column1, Column 2, etc.
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?
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
impressive!
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?
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!
Thanks for sharing👍
Thanks for watching
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.
Awesome...
First again! Love the explanation!
Yay! Thank you!
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?
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.