Remove Top Rows and Combine Data from Multiple Excel Files
Вставка
- Опубліковано 12 вер 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/l...
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/l...
✔️ Power Query Course-
goodly.co.in/l...
✔️ Master Excel Step by Step-
goodly.co.in/l...
✔️ Business Intelligence Dashboards-
goodly.co.in/l...
- - - - 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 UA-cam Channel Below
www.youtube.co...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommon...
Download Solution - www.goodly.co.in/wp-content/uploads/2021/09/Remove-Rows-and-Combine.zip
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"))))
Thanks, your tip just save my day! 🤗
@@KimHongTan Awesome! Glad to help :))
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!
Chandeep, I don't know how to thank you....this has been a lifesaver for me. Thanks a ton.
Thank you so much!! Greetings from South Africa. I love these short informative videos addressing specific every day issues one has with data.
Thanks!
Loved this one. Not only the described solution, but also the technique behind it. Thanks a lot
Amazing
Thanks a lot.. I was modifying the input files manually to remove the junk rows
This helps a lot 🙂
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.
The merged header gets unmerged automatically in Power Query
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
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.
Thank you for another wonderful tutorial, but it shall be write all column headers we have or only some of them are enough?
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?
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
Now that is a clever and useful trick! Thanks chandeep!
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.
Excellent , thanks for sharing your knowledge, Abeer from Egypt
👌. 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
Loved this video, quick and to the point - thanks
Glad you enjoyed it!
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..
Very useful indeed.
Impeccably explained.
You are really good, brilliantly good.
Glad it was useful !
Your Content is super simple
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.
Nice trick! Thanks for sharing Chandeep!
Hi Chandeep, will implement your method. My data has some blank lines also. I think your method reads all the records. Should work…..
Finally did it for cleaning the bank statement. One takeaway is that headers in data must match that are built into the custom column...
Much informative ,,people like to hear in hindi too
Sorry, it didn't work for me. I am consolidating .csv files from a folder, and unable to get rid of top 2 rows
I think CSV files are different from xlsx related to usage of M- code function at PQ
Interesting solution. Thanks Chandeep
This is next level...Top Gun!!!
Thanks!
Thank you. You are insanely genius. 👍👍👍👍
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.
Thank you so much for this - it is a genius solution!
Glad you like it 💚
Thanku for valuable awasome tutorial and how to remove nth rows for multiple excel files
Thanks Chandeep for another insightful video. How would you provide a list of quoted column names ie "Date","Category", "Value" in a Custom Column?
Great, as always and so very very useful! Thanks a lot.
Glad it was helpful!
Thanks for this informative video!! I would like to ask if you can make a video on conditional formatting chart as well.:D
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!
Mille mercis pour ce partage (et pour le fichier !) 👍
De rien
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..
Simple makes awesome. Thanks a lot
Glad you like it!
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?
The same question of me.
Thanks a lot..
Amazing!
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
Excelent 💯👍
Thanks!
Great video ! But how to deal with nth junk rows at the end of the valid data ? Best wishes from Brazil. Thank you !
Thanks!
Thanks Diana :)
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!
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!
@@GoodlyChandeep thank you!
@@GoodlyChandeep
@@GoodlyChandeep This is my friend, this video when the headers are different, did you record it? Hugs!
Thank you Chandeep 😊
What if I have to also analyse several information from the rows where there is identification information regarding group columns below.
Table.Skip(source, (x)=> x[Column1]"Date")
Nice and helpful
Glad it was helpful 😊
@@GoodlyChandeep Am becoming quite a fan of you/ your contents 😊👍🏼
Super thanks
Welcome Vidyadhar!
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?
Is it possible for me to select columns if the condition of a row is met?
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
Excellent! 👍
Outstanding!
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?
This is su f*cking useful!!! Thanks a lot for the detailed explanation.
Glad it helped!
I am getting my table empty with just headers. Applied same steps as you said @chandeep. What could be the reason.. Plz help
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
Wow..!! This is very useful :)
Nice video is their any video in Hindi for better understand
www.youtube.com/@desigoodly/videos
Awesome 👍
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
Superb
Brilliant
thumbs up!
Thanks Rence!
Magic ✨✨
Why not use index column drill down to the “value” header then remove rows and replace hard coded number with the drill down value
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
I combined the data from all the sheets and then filter out junk using using filter than loaded the data
What to do if table contains 50 columns, thn i have to write the 50 columns name into m query for check?
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
@@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 ?
for more than 3 column how to hard code that in Table.ToRecord(_) {"col1","col2","col3"......}
Use a list to define your columns!
What if you have a file with 70 columns? You type an array of 70 words?
It is pretty easy to copy the columns and surround them with curly braces and quotes.
Then stick them in the query.
@@GoodlyChandeep
Many thanks. It makes sense.
I find your videos very helpful.
Hi Goodly 🙂 I tried doing it for various csv files but the custom column reverts error 😔
Excel.Workbook() won't work for CSV files.
for CSV files - use the function Csv.Document([Content])
😃 Thank you for your reply! really appreciate it 🥰 your channel is very helpful!
Hi Chandeep
Table.Skip({data]) , each Record.ToList(_) {"Date", "null","Category") ) its not Working please help
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?
Download Solution - www.goodly.co.in/wp-content/uploads/2021/09/Remove-Rows-and-Combine.zip
Thanks a lot