Combine Multiple Excel Files with Inconsistent Column Headers the Right Way!
Vložit
- čas přidán 23. 04. 2024
- Easily combine multiple Excel files with inconsistent column headers in just four steps. And when new data is added you just need to hit refresh and your data is updated automatically even if your new data has completely different column names to your original data!
Please click here to download the practice file: docs.google.com/spreadsheets/...
✅ Please see links to videos mentioned in this video:
🎦 Power Query Advanced Editor - What Is It and How to Use It? - • Getting Started with t...
🎦 How To Use Advanced Pivot Tables to Make Data Analysis Simpler Than Ever - • How To Use Advanced Pi...
✅ Please see links to 'The Query Editor' newsletter articles mentioned in this video:
🗞️ Is Hardcoding Breaking Your Reports? Learn How to Fix It Now! thequeryeditor.beehiiv.com/p/....
🗞️ Hardcoded Column Headers Still Causing Errors? Find Out How to Mend Your Reports in Part 2! - thequeryeditor.beehiiv.com/p/....
✅ If you would like to connect on LinkedIn:
🤝 / missmicrosoft
✅ If you would like to support the channel by buying me a coffee, (it's really quick and easy), please use this link:
☕www.buymeacoffee.com/missmicr...
✅ For more videos please subscribe:
🔔 / @missmicrosoft
Your Videos are so helpful …. Thank you very much
You are most welcome, thank you for your kind words!
Missed you so much, thanks for bringing much needed content....
Thank you Anil! That put a smile on my face. :). Glad the video was helpful!
Thank you this was so clearly explained. Very useful!!
You’re most welcome Ronnie! Thank you for watching!
Thank you
You’re most welcome :)
Thank you for your valuable video as usual, i suggest to prepare a video combing some of your videos starting from dynamic path then combine data from different files with Inconsistent headers without using PQ function and avoid redecousily slow loading when dealing with a big data conatining a millions of rows.
Thank you for your kind words Mohammed! And thank you for watching! I’ve taken note, that’s a great video idea!
thank you Miss clearly explained as usual
You’re most welcome! Thank you for your kind words :)
@@MissMicrosoft please Miss can you explain how we use the dynamic segmentation to do an aged balance ( for customer) by defining rules for example (Last 30 days; Between 31 and 60 days;Between 61 and 120 days;Between 121 and 180 days....) thanks in advance Miss
Please check out this video here, not sure if this is what you require: czcams.com/video/DPr4bNFsr-4/video.htmlsi=-biq1Fr1mBaDZXeD
@@MissMicrosoft thank you miss
7:30 you crack me up 😂
😁
Thanks, as always. A quick question. Instead of combining 3 files in the current case, what happens if the files are 20 or even 30 and we also want to transform some of the column headers but there are others to be removed. Any more advanced approach to handle such complex issue?
You’re welcome Mohammed! I would suggest using lists here, you would need a renaming column list to tell Power Query what to rename the column headers to. And you would need your current header list. For the current column headers please have a look at this article on how to use the Table.ColumnNames function to dynamically grab your old column names, you would need to wrap Table.ColumnNames around Table.Combine as there are many tables that you need to get the headers from, and this would create your list of current column names. You could remove the unwanted column headers in this list. Then use List.Zip and Table.RenameColumns for the column renaming. You may need to bring in your data with the Excel.Workbook(File.Contents) function and perform the renaming in a Custom Column on the column called “Data” then expand the Custom Column for the combining. Here is the article for the renaming: thequeryeditor.beehiiv.com/p/listzip-genius-heres
I hope this helps! I will do my best to make a video on this as well.
thanks for the video. i have had this issue recently where i receive a lot of similar files but some of them changed the name of a column so the query wouldn't work. but since there are only 5 columns i did the following instead, which was very quick; in the sample file, i deleted the promote headers step, so they became the first row, which i deleted and then typed in manually the new column names. which means it doesn't matter if they send me the files with any different column names as they'll be ignored. i have downloaded your video though as i think it's interesting; i don't think i'd have been able to do it like you or i might have tried something else using the tablecolumnnames function. i might try. thanks again
Thanks for sharing Pascal! I think my next upcoming video on this topic may help you. Thank you for watching :). If you haven't yet subscribed to The Query Editor newsletter, please consider subscribing as there is an upcoming edition where I explain promoting headers, here's the link if you would like to subscribe: thequeryeditor.beehiiv.com/subscribe
@@MissMicrosoft to be clear, your videos are great and so useful; very clearly explained. love them so thanks a lot!
@@pascaljoly5752 thank you so much! I really appreciate that!
Thanks, as always. A quick question. Instead of combining 3 files in the current case, what happens if the files are 20 or even 30 and we also want to transform some of the column headers and not all. So there are some column headers to be removed. Any more advanced approach to handle such complex issue?. Plz advise
You’re welcome Mohammed! I would suggest using lists here, you would need a renaming column list to tell Power Query what to rename the column headers to. And you would need your current header list. For the current column headers please have a look at this article on how to use the Table.ColumnNames function to dynamically grab your old column names, you would need to wrap Table.ColumnNames around Table.Combine as there are many tables that you need to get the headers from, and this would create your list of current column names. You could remove the unwanted column headers in this list. Then use List.Zip and Table.RenameColumns for the column renaming. You may need to bring in your data with the Excel.Workbook(File.Contents) function and perform the renaming in a Custom Column on the column called “Data” then expand the Custom Column for the combining. Here is the article for the renaming: thequeryeditor.beehiiv.com/p/listzip-genius-heres
@@MissMicrosoft Thank you very much for the very comprehensive and timely response. This really helps. Much apprecaited.
I indeed was wait for new content from you. Sorry I just saw another Video with similar topic. Watch how Chandeep is solving it.
Renaming headers a little more advanced: czcams.com/video/0ZOY8is-bgY/video.html
Thanks Roger! And thanks for sharing Chandeep’s video, he is the Master of M!