Remove Top Rows and Combine Data from Multiple Excel Files

Here is a solution to a common problem where you can remove a fixed or variable number of top junk rows from your data before you combine it from multiple excel files or datasets.

- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

- - - - Combine Multiple Datasets Videos - - - -

---
► Artist Attribution
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)

[wayneedmondson]

Awesome Chandeep! Your solutions are always creative and efficient. Thanks for sharing how to solve this problem. Thumbs up!!
PS - Here is another method using Table.Skip() and List.PositionOf().. assuming that the column header label "Date" is not found above in the junk area of Column1 of any of the tables:
= Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Table.Skip([Data], List.PositionOf([Data][Column1], "Date"))))

[mathewinmuscat]

Chandeep, I don't know how to thank you....this has been a lifesaver for me. Thanks a ton.

[rahulkalingeri]

Amazing
Thanks a lot.. I was modifying the input files manually to remove the junk rows
This helps a lot 🙂

[DanteCry]

Man this in incredible effective and well explained! I would ask you: What if I have junk between rows (Example: junk junk data data junk junk data data). I have not been able to figure it out.

Thank you!

[ExcelWithChris]

Thank you so much!! Greetings from South Africa. I love these short informative videos addressing specific every day issues one has with data.

[dirkstaszak]

Loved this one. Not only the described solution, but also the technique behind it. Thanks a lot

[Sean_Yemen]

Awsum Chandeep! You make it look so simple ;).
A question though, how will merged header cells affect the query application?
Thank you for the teaching.

[KgasS]

👌. Normally PQ takes care of the field orders while appending if the data set is clean. If the field order is changed for example in one sheet change the order of Category and Value, the combined data result is different. A check for contains may be required in that case. Thanks

[SamiElzaim]

Now that is a clever and useful trick! Thanks chandeep!

[MrAbrandao]

Fantástic work. But as ever, "we want more."
Would be nice not to have to create a list with all the headers, but just one from a specific column.
Also would be nice to remove empty columns..

[abeerattia]

Excellent, thanks for sharing your knowledge, Abeer from Egypt

[Drew-vp]

Great video. Thanks so much. Let's say you had the same data file but the junk rows had a date of the report which you would like to add as a column to the table. So you would like to add the date to each record as another column and then delete the junk rows. What's the best way to do that?

[moonrollingpapers]

HI, Thanks for the video, what about mutliable workbook instead of sheet in one workbook. when I do it it show "record" instead of " table" which we need to work on it

[Nitin-ydko]

Much informative, ,people like to hear in hindi too

[karanbirsoin]

Hi Chandeep, will implement your method. My data has some blank lines also. I think your method reads all the records. Should work…..

[gennarocimmino]

Very useful indeed.
Impeccably explained.
You are really good, brilliantly good.

[cristian.angyal]

Nice trick! Thanks for sharing Chandeep!

[PrakashFatania]

Loved this video, quick and to the point - thanks

[jianlinchen]

Table.Skip(source, (x)=> x[Column1]<>"Date")

[seshakv]

Thank you. You are insanely genius. 👍👍👍👍