Combine Data from Multiple Excel Files with Inconsistent Column Names

In this video, we will learn how we can combine data from multiple excel files which contain different column names. We will also learn how to create a mapping table & plug that as the rename the inconsistent column names!

===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

===== LINKS 🔗 =====

===== CONTACT 🌐 =====

===== CHAPTERS =====
0:00 Intro
0:20 Explanation of the Problem & Data
1:27 Understanding the Logic
3:55 Getting all the Column Names
8:01 Creating the Mapping Table in Excel
9:28 Combining the Data & plugging in the Mapped Table
15:47 Testing the Query
17:41 My Courses

===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
Official After The Fall YouTube Channel Below
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)

[attaturk]

I don't understand why this channel isn't bigger, seeing that the solutions he gives is by far explained in such a way that most people understand. Keep up the good work Sir.

[shyamadasgupta]

I am sure this is helpful to the excel experts. As a layman, I wanted step by step solution. Like starting right with the mapping tables. Everything was already created earlier, so i could not understand anything.

[richardmartin]

Wanted to let you know this proved useful to me. My case was far more complex and I had to debug somethings...but it worked! Saves me like 90 minutes of work.

[VirgilioAlbertoCardonaFajardo]

You're a lifesaver, I'm just getting started into Query logic and your channel has been tremendously helpful!

[rajugsg]

You are a MASTER of excel. Glad that I found your channel and is really informative/educational and highly useful.
This channel deserves a millions of subscribers 🎉❤😊

[ahsaaah]

Hi, thank you for such a clear explanation. I hope you can help, not many people talk about this, but can we load each table as a separate data table? Or is it possible to horizontally combine all the tables if the date column is the same in all tables loaded here?

[rachelle]

This really helps me out a lot honestly! I had been checking here and there but nowhere to be found the best way out of my problem till i checked this video out! Amazing❤❤❤

[mangixism]

Hey Chandeep, thanks for the really good videos. It's really fun to listen to you and above all you explain everything really well. Keep up the good work!

[jerrydellasala]

Agree, Outstanding. NOTE: The Sales Data folder in the Zip file has two temporary files - names starting with ~ that are hidden and causing an error in the M Code. Just delete them to fix it. Also, change the folder path in the FolderLocation query, not the Data Source.

[robkingston]

This rocks! Thank you for a great explanation on this topic!

[anilb]

Thank you very much. I was working on such exercise since last 2 weeks and struggle a lot, referred many You tube videos but.... Hats up to you dear ...which helped to proceed further ..Very well explained ....

[katiegordham]

This was an EXCELLENT tutorial, thank you so much.

[excelemployeeleavetracker]

This was great as always. I have been doing this another way without Table.Combine and List function.I need to wrap my head around Table.Combine and List as this would make it more dynamic.

[kuuuyajim]

Thank you for this tutorial...it's the perfect solution to the issue we are having with 100+ columns.

[mrtfia]

Your videos are brilliant and you're so inspirational. You make it easy to understand the logic and then the code to apply the logic. This video has really helped me and I'd like to say a big THANK YOU !

[jawadahmadehssan]

Thank you Chandeep. This is a common challenge faced while working with different data sets.

[tuomiallen]

I'm going to try this in my next assignment! Thank you! For the potential risk of wrongly aligned mapping table after a new file with new headers getting added, I wonder if a better way of doing it is to create a mapping table to be left joined to the dynamic column header table? It will still shows empty if a new table with new header gets added, but at least it won't map to the wrong thing after refreshed.

[calpoltab]

wonderful really, I had a tough time playing around inconsistent columns. A big thankyou 🙂

[emilmubarakshin]

This is soo amazingly helpful! Thank you!

[emanuelecostantinocatanzar]

This video is really GREAT. Thank you Chandeep for your outstanding way to explain the solutions.