Automatically Remove Top Junk Rows & Combine Data from Multiple Excel Files

In this video, we will learn to remove junk rows from the top and combine data from multiple Excel files. We will explore three Excel files, each containing a few unwanted rows at the top. Our goal is to remove this inconsistent number of junk rows and merge the data from all the files automatically.

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

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

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

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

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

[nelson_k_d]

I saved the entire syntax as follows:

Add Custome Column =Table.PromoteHeaders(Table.Skip([Data], each not List.ContainsAny( Record.ToList(_), {"Header1", "Header2"} )))

Works wonders!!
Thanks a ton bro!!

[goodnewskasparyaodzramedo]

This is wonderful @Goodly. I watch all your videos. From the logic to the solution of the problem and the actual solution. God continually bless you, you are a messiah!

[OmisileKehindeOlugbenga]

Pretty awesome! Thanks a lot for this. Record, Table and List object manipulation in one video for one task without using "Remove Other Columns"

[tARasKoni]

M-Masterpiece!
Packing a lot of slick tricks in one video.
Thank you Chandeep!

[ramonillarramendi]

Wow..Amazing. Been struggling with removing dynamically the junk and with custom headers for a while now. This works like a charm. Thanks a mill.

[stuartproctor]

Wow, this came up in my feed and it’s exactly what I’ve struggling with all week!!! You’re an absolute star, thank you! 🙌🏻☺️👌🏻

[retamapark]

Thanks, Chandeep! I knew you had posted this, and I had this problem today. I was fooling around with different other approaches that were a mess. This was perfect!

[josepepe]

It is a huge pleasure to look at your videos. Moving from excel advanced user to Power query person. Thanks a lot.

[decentmendreams]

Your methods become so refined over time . Awesome job

[inaction]

This is a lifesaving technique. Thank you for sharing with us.

[vli]

Super Awesome, Chandeep.
Very powerful formulas that you are teaching in a simple and easy understandable way !
Power Query and DAX are having lot of hidden treasures

[YajuvendrasS]

Thanks Chandeep! I was using Index for this one, but you make it so easy. Learning from your videos is amazing! Keep up the good work!

[Phippsy]

Amazing, I've done this with a fixed Skip value but this is on another level! Thanks

[pmsocho]

Awesome. Great logic. Thanks for the video.

[sharadpunita]

I am 70 having no use of learning Excel/Power query! I keep watching your videos as I like the way you solve problems. I have learnt so much from your teaching. Can I pay some Token for whatever I have learnt.

[odallamico]

Dear, you are a genius. You make M language look so easy. I appreciate your videos, my respects to you.

[justinwduff]

Thank you, SO MUCH! Had about 200 files to combine with various junk rows up top and now I can do it :D

[Chillman]

You have no idea how much Power Query has helped me to automate my tasks. Also I have been struggling with this problem. A big THANK YOU ❤!

[cherianiype]

ooff!! SHABASH! Terrific video Chandeep! Superb!! Sixer Maar diya!

[rajanpradeepankarath]

yeah that is pretty damn awesome, Chandeep. this is an everyday challenge