Remove Top Rows and Combine Data from Multiple Excel Files

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
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 -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
- - - - Combine Multiple Datasets Videos - - - -
Unpivot and Combine - • Unpivot and Combine Da...
Dynamic Combine Multiple Excel Files - • Combine Data From Mult...
Dynamic Combine Multiple Excel Sheets - • Combine Data from Mult...
---
► Artist Attribution
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ​
Official After The Fall KZbin Channel Below
kzbin.info/door/GQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

[@GoodlyChandeep]

Download Solution - www.goodly.co.in/wp-content/uploads/2021/09/Remove-Rows-and-Combine.zip

[@wayneedmondson1065]

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"))))

[@KimHongTan]

Thanks, your tip just save my day! 🤗

[@wayneedmondson1065]

@@KimHongTan Awesome! Glad to help :))

[@dirkstaszak4838]

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

[@mathewinmuscat]

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

[@ExcelWithChris]

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

[@GoodlyChandeep]

Thanks!

[@rahulkalingeri1206]

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

[@PrakashFatania]

Loved this video, quick and to the point - thanks

[@GoodlyChandeep]

Glad you enjoyed it!

[@SamiElzaim]

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

[@cristian.angyal]

Nice trick! Thanks for sharing Chandeep!

[@abeerattia4523]

Excellent , thanks for sharing your knowledge, Abeer from Egypt

[@ashokkumar-qy3ph]

Your Content is super simple

[@GrainneDuggan_Excel]

Interesting solution. Thanks Chandeep

[@seshakv]

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

[@gennarocimmino]

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

[@GoodlyChandeep]

Glad it was useful !

[@EricaDyson]

Great, as always and so very very useful! Thanks a lot.

[@GoodlyChandeep]

Glad it was helpful!

[@DanteCry23]

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!

[@learningforever1195]

Thanks for another informative tutorial. This saves a lot of steps and simplifies it. An ask - In case of the excel file(s) with sub-totals in it, its been observed that the header record is not available and needs to be addressed manually. Is there any way around for same without manually removing the sub-totals from each excel file before importing in Power query. Thanks.

[@epernayjfl]

Mille mercis pour ce partage (et pour le fichier !) 👍

[@GoodlyChandeep]

De rien

[@Nitin-yd5ko]

Much informative ,,people like to hear in hindi too

[@vineetyoutubr5]

Thank you Chandeep 😊

[@DavidWarbeck]

Thank you so much for this - it is a genius solution!

[@GoodlyChandeep]

Glad you like it 💚

[@mnowako]

Excellent! 👍

[@sircorn4248]

Simple makes awesome. Thanks a lot

[@GoodlyChandeep]

Glad you like it!

[@jjohn108]

Wow..!! This is very useful :)

[@BestICan]

Outstanding!

[@eslamfahmy87]

Thank you for another wonderful tutorial, but it shall be write all column headers we have or only some of them are enough?

[@abdullah_alhathloul_]

Thanks a lot..

[@pranoysingha3236]

Thanks a lot

[@pdadey]

Thanks Chandeep for another insightful video. How would you provide a list of quoted column names ie "Date","Category", "Value" in a Custom Column?

[@brianxyz]

Amazing!

[@rrrraaaacccc80]

Excelent 💯👍

[@GoodlyChandeep]

Thanks!

[@anshikapandey2977]

Thanks for this informative video!! I would like to ask if you can make a video on conditional formatting chart as well.:D

[@rajanmehandiratta596]

Hi Chandeep, Thank you for this wonderful video. What if number of columns are more, then it's quite tedious to put all the column names manually in the list. Is there any way to link it with one list having detail of all the column headers to make it dynamic.

[@moonrollingpapers6521]

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

[@brij26579]

Awesome 👍

[@santoshpv321]

This is next level...Top Gun!!!

[@GoodlyChandeep]

Thanks!

[@dianaadams5026]

Thanks!

[@GoodlyChandeep]

Thanks Diana :)

[@seanw4687]

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.

[@GoodlyChandeep]

The merged header gets unmerged automatically in Power Query

[@vidyadharvm4132]

Super thanks

[@GoodlyChandeep]

Welcome Vidyadhar!

[@jpvramanarao1900]

Thanku for valuable awasome tutorial and how to remove nth rows for multiple excel files

[@shenuforu]

Thanks a lot.. am currently workin on an assignment where this helps me however i hav one more instance where the heading comes bac again at the bottom wit few more values and currently my query ignores that.. is there a way i can apply the same principle multiple times so it picks tht data as well.. pls help..

[@manasa5639]

Nice and helpful

[@GoodlyChandeep]

Glad it was helpful 😊

[@manasa5639]

@@GoodlyChandeep Am becoming quite a fan of you/ your contents 😊👍🏼

[@smartwork4768]

Hello Genius, I never miss your any of the videos and you are absolutely amazing in this. Well, i have one query if you could guide me here, - Let's say i have load query from folder and i have multiple files available in that folder belongs to every month sales and let's say i have built one report and loaded in excel using power query. - Now after deleting a few files from folder, how could i keep old file data in excel where i have loaded the query besides that file get removed from folder? - as we know after refracing of query it would load fresh data only in model. basically, i want to preserve old data also in my excel where i have load the query. Thanking you in advance.

[@shoaibrehman9988]

Superb

[@The_Code_Father]

Very smart mr chandeep but i was wondering what if columns header titles are dynamic and I make them data driven not manually insert them into the formula

[@SuperUddie]

Hello Chandeep! Thank you so much for the detailed explanation in this video and all the other videos which you record. I was able to unblock myself in multiple occasions following through your videos. Could you please help me on the below problem I am facing. I have a scenario where I need to combine 12 .xlsb (binary files) and each of these files have more than 300 columns. When I load these files into Power BI and try to combine, I am able to get only 255 columns. Is there any way where I can combine these files and get more than 255 columns (300 columns) Thank you again!

[@bravucod]

Brilliant

[@kirshnannagarajan8756]

It’s useful. Can you show how to pick files from share point with junk row and dynamic header. So I need to call out all the header name

[@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

[@polemack]

Great video ! But how to deal with nth junk rows at the end of the valid data ? Best wishes from Brazil. Thank you !

[@SamehRSameh]

Magic ✨✨

[@aahmed7749]

I am importing data from multiple excels in a folder. Each of them has two sheets, I just need first one. I was able to import data but when I use your formula to remove top 4 rows, it didn't work. I tried to do it before loading data (by going directly to EDIT option) and still didn't work.

[@dorotkak.7598]

What if I have to also analyse several information from the rows where there is identification information regarding group columns below.

[@koteswaribapatla8075]

Hi,if I have more no of columns it is difficult to enter manually so how to automate even header names comparision with junk columns

[@karanbirsoin9983]

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

[@karanbirsoin9983]

Finally did it for cleaning the bank statement. One takeaway is that headers in data must match that are built into the custom column...

[@icyknightster12]

HI Your video and explanation is very details. But I have issue that cant extract the table data on my 3 workbooks. I have header started from 8th row and I tried to replace the " each Record.ToList(_) {'Date', 'Category', 'Value'} " from Date to let say "Device owner" etc to my own but it couldnt show any table info. Any advise?

[@dyanzzz]

Do you know how to remove specific rows like for example i have 25 rows and i want to delete rows 6,9, 12-18... how to do that?

[@cheikhhakim5864]

please Chandeep how can we do if the table to combine have differents headers how can we match them in M language before to combine

[@rencerivera9171]

thumbs up!

[@GoodlyChandeep]

Thanks Rence!

[@rlds07]

Is it possible for me to select columns if the condition of a row is met?

[@pk5134]

Thanks Chandeep. This was very useful! In this example the column headings were the same. How would you use this technique when the inner tables are different, say for example, the tables are Sales, order, product and, territory, where the column names are not the same. One way could be searching for a blank row before the main data begins. But this wouldn't work when there are no blank rows between the junk rows and the main data table Also, one concept I find hard to grasp is the use of _ and where it can be used, and how it differs from the "each" keyword. Could you please do a video on it ? Thanks!

[@GoodlyChandeep]

If the headers are different, I would then look for a completely filled record (which will be the header row). I'll post a video on each and _ soon. Thanks for the suggestion!

[@pk5134]

@@GoodlyChandeep thank you!

[@rlds07]

@@GoodlyChandeep

[@rlds07]

@@GoodlyChandeep This is my friend, this video when the headers are different, did you record it? Hugs!

[@priyankabhutada6198]

I am getting my table empty with just headers. Applied same steps as you said @chandeep. What could be the reason.. Plz help

[@d20207]

This is su f*cking useful!!! Thanks a lot for the detailed explanation.

[@GoodlyChandeep]

Glad it helped!

[@brij26579]

What if i have more than 10 columns & want to keep a check only on 1st column = Date to identify 1st Row & delete all previous rows

[@RaviGupta-mo1nf]

Lets add another variable to the problem. There is a 4th or 5th column in one or more sheets. These additional columns need not be named same across sheets. I want to ONLY combine only 3 columns which I select either manually or by making a list. How do I do that?

[@md.raselhossain5086]

The same question of me.

[@vijaykumarjain3222]

Nice video is their any video in Hindi for better understand

[@GoodlyChandeep]

www.youtube.com/@desigoodly/videos

[@jianlinchen7978]

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

[@alidaas]

Sorry, it didn't work for me. I am consolidating .csv files from a folder, and unable to get rid of top 2 rows

[@eslamfahmy87]

I think CSV files are different from xlsx related to usage of M- code function at PQ

[@pradnyajadhav189]

What to do if table contains 50 columns, thn i have to write the 50 columns name into m query for check?

[@cristian.angyal]

Regardless of the number of rows you could always have a separate table with the Names of the columns, import it to PQ, make it a list and use it INSEATD of the manually input list shown by Chandeep in the video

[@RaviGupta-mo1nf]

@@cristian.angyal Yes. This is what I did too. Make a List of Headers by having another query from any file. Is there a better way ? Also...What if the data does not start from Column A ? How do we deal with this ?

[@gabrielabraham5512]

Hi Chandeep

[@gabrielabraham5512]

Table.Skip({data]) , each Record.ToList(_) {"Date", "null","Category") ) its not Working please help

[@iamdonna5918]

Hi Goodly 🙂 I tried doing it for various csv files but the custom column reverts error 😔

[@GoodlyChandeep]

Excel.Workbook() won't work for CSV files. for CSV files - use the function Csv.Document([Content])

[@iamdonna5918]

😃 Thank you for your reply! really appreciate it 🥰 your channel is very helpful!

[@Rkeev1]

Why not use index column drill down to the “value” header then remove rows and replace hard coded number with the drill down value

[@dirkstaszak4838]

As they say many ways lead to Rome. At the end of the day the choice would be on the more performant solution. There is no right or wrong

[@vashisht1]

I combined the data from all the sheets and then filter out junk using using filter than loaded the data

[@LaloinLondon]

What if you have a file with 70 columns? You type an array of 70 words?

[@GoodlyChandeep]

It is pretty easy to copy the columns and surround them with curly braces and quotes. Then stick them in the query.

[@LaloinLondon]

@@GoodlyChandeep Many thanks. It makes sense. I find your videos very helpful.

[@krishnamanjunatha6183]

for more than 3 column how to hard code that in Table.ToRecord(_) {"col1","col2","col3"......}

[@GoodlyChandeep]

Use a list to define your columns!

[@Simon-vc1wk]

I have a similar scenario. The range of files I have (I dont have control over) will contain duplicate months in different workboooks, some files have single month other have month to date. I only want to import a single instance of a given month. My exception however is the meta data for my various files isn't unique other I'd use this to import my files. My thoughts is to count occurance of each list and exclude >2 or create a key (combination of all fields in ach file). Are there other ways to exclude duplicate months?

[@chandeepchhabra]

Download Solution - www.goodly.co.in/wp-content/uploads/2021/09/Remove-Rows-and-Combine.zip