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"))))
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!
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
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?
👌. 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
Amazing! What if the junk rows are actually below the table? For example, after financial statements, you may financial the notes to the financial statements. How do you remove those so you remain only with the statements themselves? Also, I have seen instances where there is "junk" to the right of the table, such as people commenting on specific rows, like why is this too high is this too low, referring to figures on the left in the table.
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.
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.
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.
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.
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!
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..
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..
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.
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
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!
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?
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?
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 ?
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
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!
Amazing
Thanks a lot.. I was modifying the input files manually to remove the junk rows
This helps a lot 🙂
Loved this one. Not only the described solution, but also the technique behind it. Thanks a lot
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
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?
👌. 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
Thank you for another wonderful tutorial, but it shall be write all column headers we have or only some of them are enough?
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
Amazing! What if the junk rows are actually below the table? For example, after financial statements, you may financial the notes to the financial statements. How do you remove those so you remain only with the statements themselves? Also, I have seen instances where there is "junk" to the right of the table, such as people commenting on specific rows, like why is this too high is this too low, referring to figures on the left in the table.
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.
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.
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.
Now that is a clever and useful trick! Thanks chandeep!
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 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...
Your Content is super simple
Excellent , thanks for sharing your knowledge, Abeer from Egypt
Nice trick! Thanks for sharing Chandeep!
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!
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..
Thanku for valuable awasome tutorial and how to remove nth rows for multiple excel files
Loved this video, quick and to the point - thanks
Glad you enjoyed it!
Thanks Chandeep for another insightful video. How would you provide a list of quoted column names ie "Date","Category", "Value" in a Custom Column?
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 !
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.
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
Great video ! But how to deal with nth junk rows at the end of the valid data ? Best wishes from Brazil. Thank you !
Interesting solution. Thanks Chandeep
Thanks for this informative video!! I would like to ask if you can make a video on conditional formatting chart as well.:D
Thank you. You are insanely genius. 👍👍👍👍
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!
Much informative ,,people like to hear in hindi too
What if I have to also analyse several information from the rows where there is identification information regarding group columns below.
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?
Thank you so much for this - it is a genius solution!
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.
Great, as always and so very very useful! Thanks a lot.
Glad it was helpful!
Mille mercis pour ce partage (et pour le fichier !) 👍
De rien
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
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?
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
Thank you Chandeep 😊
This is next level...Top Gun!!!
Thanks!
Simple makes awesome. Thanks a lot
Glad you like it!
I am getting my table empty with just headers. Applied same steps as you said @chandeep. What could be the reason.. Plz help
Is it possible for me to select columns if the condition of a row is met?
Table.Skip(source, (x)=> x[Column1]"Date")
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
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
Excelent 💯👍
Thanks!
Excellent! 👍
Wow..!! This is very useful :)
Amazing!
Outstanding!
Thanks a lot..
Nice and helpful
Glad it was helpful 😊
@@GoodlyChandeep Am becoming quite a fan of you/ your contents 😊👍🏼
Thanks!
Thanks Diana :)
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
Nice video is their any video in Hindi for better understand
www.youtube.com/@desigoodly/videos
I combined the data from all the sheets and then filter out junk using using filter than loaded the data
Awesome 👍
Super thanks
Welcome Vidyadhar!
This is su f*cking useful!!! Thanks a lot for the detailed explanation.
Glad it helped!
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 ?
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!
Superb
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.
for more than 3 column how to hard code that in Table.ToRecord(_) {"col1","col2","col3"......}
Use a list to define your columns!
Brilliant
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?
Magic ✨✨
Download Solution - www.goodly.co.in/wp-content/uploads/2021/09/Remove-Rows-and-Combine.zip
thumbs up!
Thanks Rence!
Hi Chandeep
Table.Skip({data]) , each Record.ToList(_) {"Date", "null","Category") ) its not Working please help
Thanks a lot