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

@KimHongTan

2 жыл бұрын

Thanks, your tip just save my day! 🤗

@wayneedmondson1065

2 жыл бұрын

@@KimHongTan Awesome! Glad to help :))

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

Much informative ,,people like to hear in hindi too

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

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!

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

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!

Nice trick! Thanks for sharing Chandeep!

Excellent , thanks for sharing your knowledge, Abeer from Egypt

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

Interesting solution. Thanks Chandeep

Loved this video, quick and to the point - thanks

@GoodlyChandeep

Жыл бұрын

Glad you enjoyed it!

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

Your Content is super simple

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

@GoodlyChandeep

Жыл бұрын

Glad it was useful !

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..

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

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.

Thank you Chandeep 😊

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, as always and so very very useful! Thanks a lot.

@GoodlyChandeep

Жыл бұрын

Glad it was helpful!

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

@GoodlyChandeep

2 жыл бұрын

De rien

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

@GoodlyChandeep

Жыл бұрын

Thanks!

Excellent! 👍

Simple makes awesome. Thanks a lot

@GoodlyChandeep

2 жыл бұрын

Glad you like it!

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

Thanks a lot..

Thanks a lot

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

@GoodlyChandeep

2 жыл бұрын

Glad you like it 💚

Outstanding!

Amazing!

Wow..!! This is very useful :)

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

👌. 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

Awesome 👍

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

Thanks!

@GoodlyChandeep

Жыл бұрын

Thanks Diana :)

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

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.

Super thanks

@GoodlyChandeep

Жыл бұрын

Welcome Vidyadhar!

Excelent 💯👍

@GoodlyChandeep

Жыл бұрын

Thanks!

Superb

Brilliant

Nice and helpful

@GoodlyChandeep

2 жыл бұрын

Glad it was helpful 😊

@manasa5639

2 жыл бұрын

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

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

2 жыл бұрын

The merged header gets unmerged automatically in Power Query

Magic ✨✨

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..

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...

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.

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.

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!

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

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

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

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?

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

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

@GoodlyChandeep

Жыл бұрын

Glad it helped!

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

Nice video is their any video in Hindi for better understand

@GoodlyChandeep

7 ай бұрын

www.youtube.com/@desigoodly/videos

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

thumbs up!

@GoodlyChandeep

Жыл бұрын

Thanks Rence!

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

2 жыл бұрын

The same question of me.

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?

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

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

2 жыл бұрын

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

2 жыл бұрын

@@GoodlyChandeep thank you!

@rlds07

Жыл бұрын

@@GoodlyChandeep

@rlds07

Жыл бұрын

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

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

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

Hi Chandeep

@gabrielabraham5512

Жыл бұрын

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

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

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

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

@cristian.angyal

2 жыл бұрын

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

2 жыл бұрын

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

@GoodlyChandeep

2 жыл бұрын

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

@iamdonna5918

2 жыл бұрын

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

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

2 жыл бұрын

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

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

@GoodlyChandeep

2 жыл бұрын

Use a list to define your columns!

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.

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