Automatically Remove Top Junk Rows & Combine Data from Multiple Excel Files

Подписаться 89 тыс.

Просмотров 35 тыс.

50% 1

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, we will learn to remove junk rows from the top and combine data from multiple Excel files. We will explore three Excel files, each containing a few unwanted rows at the top. Our goal is to remove this inconsistent number of junk rows and merge the data from all the files automatically.
===== ONLINE 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...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/automatically-re...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

Наука

Опубликовано:

15 июн 2023

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 118

@josepepe741 6 дней назад

It is a huge pleasure to look at your videos. Moving from excel advanced user to Power query person. Thanks a lot.

@nelson_k_d Месяц назад

I saved the entire syntax as follows: Add Custome Column =Table.PromoteHeaders(Table.Skip([Data], each not List.ContainsAny( Record.ToList(_), {"Header1", "Header2"} ))) Works wonders!! Thanks a ton bro!!

@OmisileKehindeOlugbenga Год назад

Pretty awesome! Thanks a lot for this. Record, Table and List object manipulation in one video for one task without using "Remove Other Columns"

@odallamico Месяц назад

Dear, you are a genius. You make M language look so easy. I appreciate your videos, my respects to you.

@tARasKoni Год назад

M-Masterpiece! Packing a lot of slick tricks in one video. Thank you Chandeep!

@Phippsy23 Год назад

Amazing, I've done this with a fixed Skip value but this is on another level! Thanks

@retamapark 6 месяцев назад

Thanks, Chandeep! I knew you had posted this, and I had this problem today. I was fooling around with different other approaches that were a mess. This was perfect!

@ramonillarramendi3191 7 месяцев назад

Wow..Amazing. Been struggling with removing dynamically the junk and with custom headers for a while now. This works like a charm. Thanks a mill.

@rhaps2008 Год назад

Amazing, I have had to struggle through with this exact issue to manually remove those junk rows, your a life saver I will be using this in the future

@roywilson9580 Год назад

Thanks for the video. Much better than only skipping rows to one hardcoded value, makes sense to use if your column order is not the same across data tables.

@decentmendreams 10 месяцев назад

Your methods become so refined over time . Awesome job

@santiagovillamoreno6821 Год назад

Beautiful Power Query techniques!!

@iankr Год назад

Brilliant! Many thanks, Mr Goodly.

@inaction2024 8 месяцев назад

This is a lifesaving technique. Thank you for sharing with us.

@ursvenky6394 Год назад

Awesome video. I was struggling earlier. I had work by using macro. This is very cool. Thanks Goodly

@goodnewskasparyaodzramedo9097 Год назад

This is wonderful @Goodly. I watch all your videos. From the logic to the solution of the problem and the actual solution. God continually bless you, you are a messiah!

@aahanavikram07 Год назад

You hear the problem it seems😂 I was using filter method and removing null values and a lot of other filter method. Thanks for making the work easier and cleaner ❤

@vl21i Год назад

Super Awesome, Chandeep. Very powerful formulas that you are teaching in a simple and easy understandable way ! Power Query and DAX are having lot of hidden treasures

@rohitwaradpande2492 Год назад

This was awesome video. Thanks for the same. I liked the trick that you used to removed the blanks dynamically.

@pmsocho 4 месяца назад

Awesome. Great logic. Thanks for the video.

@joukenienhuis6888 10 месяцев назад

Thanks again for such a great clear videobabout the next step in PowerQuery. I am new in PowerQuery, but i am experimenting on DAX and you are giving a great explanation

@rajanpradeepankarath8846 Год назад

yeah that is pretty damn awesome, Chandeep. this is an everyday challenge

@bohdanduda652 Год назад

Another way, how we can do it, it is add additional column using List.PositionOf and due to that calculate in which position we have Date and Profit

@user-sn8vk9lt4o 4 месяца назад

I am actually learning power query its Excellent. I like the way to teach. Thank u so, Much for this video.

@justinwduff 11 месяцев назад

Thank you, SO MUCH! Had about 200 files to combine with various junk rows up top and now I can do it :D

@GoodlyChandeep 11 месяцев назад

Woah.. thats a lot of files. I am glad I could help

@syrophenikan Год назад

Fantastic! This is definitely going into my daily routine.

@GoodlyChandeep Год назад

Enjoy! 😉

@zahir585 Год назад

Genius 🔥 thank you my friend sooo helpful ❤️❤️❤️

@ShubhamSharma-ls6hj Год назад

Very well explained thanku so much brother.

@davidjosevarelagarcia7011 Год назад

Great , excellent. Simple like that. Thanks.

@Chillman666 Год назад

You have no idea how much Power Query has helped me to automate my tasks. Also I have been struggling with this problem. A big THANK YOU ❤!

@GoodlyChandeep Год назад

Happy to help! 😉

@setantadundalk Год назад

Very sleek. I had this very same issue but I used List.Generate to loop through each record which suppose would take slightly more processing time but nothing you would ever notice.

@manojsakthi6201 11 месяцев назад

its very awesome, i hade a similar issue had to work around it, but this looks pretty good

@jianlinchen7978 2 месяца назад

really very helpful. Thanks . It is a good idea

@rahulmahajan5932 Год назад

Awe Stucked... No Words to Express How Fantabulous It is

@GoodlyChandeep Год назад

Thanks Rahul :)

@Rice0987 Год назад

Magosh... It's just simple brilliant!💎 Thanks a bunch for yor priceless help!🤗👦

@GoodlyChandeep Год назад

Thanks :)

@Ratnakumarwrites Год назад

Super Video Chandeep.

@stefankirst3234 Год назад

Really awesome!

@chrism9037 Год назад

Fantastic Chandeep, thank you!

@GoodlyChandeep Год назад

Glad you liked it !

@tiagocarvalhal4502 Год назад

Incredible. Thanks :)

@user-tp3jq8qg8l 5 месяцев назад

admiring the brilliance

@stephenandrews1291 7 месяцев назад

this is awesome; thank you! one question - i need to add a column into the combined file that shows the original source filename for each record... where in the flow and how best to do that please?

@swathimanda5342 Год назад

Hi Goodly, That was an amazing video. I learned a lot from your videos for my daily tasks with excel. It saves lot of my time. God bless you. I have a question please if you can answer that, when I covert pdf to excel most of the column values are not aligned into 1 column but locate on either side. Ex: column B dates, should be in column B but on few rows it will be on A or C. How can I align them into just 1 column B. Please advise. Thank you for all your great videos. 🙏

@emilmubarakshin49 10 месяцев назад

Fantastic video and amazing explanation!

@GoodlyChandeep 10 месяцев назад

Many thanks :)

@gennarocimmino Год назад

I would say simply amazing !!!!

@arbazahmad7177 Год назад

Fantastic 🎉.. Thanks 😊

@MrKamranhaider0 Год назад

JUST AMAZAING SUPERB

@Donkeys_Dad_Adam Месяц назад

This is great! What is the best way to do this when your source files are not formatted as Tables, but are simply Excel Worksheets?

@zeinh5139 11 месяцев назад

Wow ! thank you

@cherianiype Год назад

ooff!! SHABASH! Terrific video Chandeep! Superb!! Sixer Maar diya!

@GoodlyChandeep Год назад

Thanks 😉

@mohamedheltoukhy2355 9 месяцев назад

Amazing Thanks alot

@hassanjatta4257 Год назад

Awesome!!!

@wensesvincen4877 Год назад

Hi Goodly, Wonderful and Powerful Trick...Keep up the good work. What if you are importing from PDF files...Trying to convert the binary gives a different results.

@excel-in-g Год назад

As always, very neat & clear stuff. 👍 I was wondering if one can't use Table.FindText? Like, each Table.Skip( _,Table.PositionOf(_, Table.FindText(_, "Profit"){0} ))) But only testing for 1 column header here.

@RogerStocker Год назад

unbelievable crazy as usual.

@j_baisley_ 5 месяцев назад

Damn awesome is right 👍

@mohitchaturvedi8931 Год назад

Delighted, this is the problem of every hour. Many times data come with merged header, which you have sorted already,

@noorbisharmohamed Год назад

Fantastic! But can we use "is blank" instead of "not Contain any" as the condition? Or promote headers if record contains any " Date", "Amount" etc?

@eslamfahmy87 11 месяцев назад

Actually, I think you are the only youtuber instructor who is preparing depth & creative PQ examples. Really, fantastic 👏, elegance & more simple 😊

@abhijitmodak3461 6 месяцев назад

Thanks!

@GoodlyChandeep 6 месяцев назад

Thanks a lot Abhijit :)

@itgyantricks7218 11 месяцев назад

excellent

@rogeriopsvalle 9 месяцев назад

Hi Goodly. Thanks for all videos. They are just great. I need to combine two of your tricks in just one. I have many sheets with junk lines (same number of junk lines for all sheets) and these same sheets have inconsistent columns. How do I do that? Thanks in advance

@GoodlyChandeep 8 месяцев назад

You code to remove the junk headers can probably be Table.Skip ( Table, List.MatchesAny(Record.ToList(_), each _ "null" or each _ "") After this you can promote the headers and then follow the inconsistent header video.

@cooolbreeze 10 месяцев назад

Pretty cool. Seems the only thing that would limit what one can do with PQ is one's imagination. Question, why List.ContainsAny instead of List.ContainsAll?

@denissipchenko2455 Год назад

Thanks for sharing! But looks like there should be List.ContainsAll instead of List.ContainsAny

@excel-in-g Год назад

Was thinking the same.

@bulbulahmed4414 Год назад

Amazing

@josealvesferreira1683 9 месяцев назад

very nice

@ankursharma6157 11 месяцев назад

Token of Gratitude!

@GoodlyChandeep 11 месяцев назад

Thank you so much Ankur

@andreass.3130 Год назад

Hi Goodly, thanks for all your great videos. Isn't there a simpler way to do it here? In the example file you create a conditional column (If Column1=Date Then True). Then you fill the conditional column downwards. Now you have a True for all rows you need and a null value above the desired header row. So you can filter for True. Shouldn't that be dynamic as well?

@DAXifiedSatish Год назад

No. In that way if u filter true it will be with junk rows and all data except headers and if u filter for false u will get only headers

@udayteja6595 9 месяцев назад

Great

@retamapark 6 месяцев назад

Not as sophisticated, but say you had a table with two columns, Col1 and Col2. There are junk rows at the top. The row with headers has the values "Date" and "Amount". This seems for work and is easy to implement. let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Custom1 = Table.Skip(Source, each [Col1] "Date" and [Col2] "Amount") in Custom1

@retamapark 6 месяцев назад

fnTableSkipDynamic(Source, "Col1", "Date", "Col2", "Amount") Function to do this: Input column names as text, header values as text. (sourcetablename as table, col1name as text, header1value as text, col2name as text, header2value as text)=> let return = Table.Skip(sourcetablename, each ( Record.Field(_, col1name) header1value) and ( Record.Field(_, col2name) header2value) ) in return

@GoodlyChandeep 6 месяцев назад

List.matchesall can look for both the headers. 👍

@SandhyaSingh-qk6up Год назад

Can this also be solved by using index number and custom function?

@patrickharilantoraherinjat2994 Год назад

Great video Goodly ! what if I have a junk rows and also a junk columns. is it possible to combine? Thanks

@GoodlyChandeep Год назад

Thanks Patrick. May this video will help ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-1fn8fXYw6M4.html

@starprinceofficial9603 2 месяца назад

For me my question if we need to bring the data before the columns as a new column before promoting headers, how do we go about it

@TomStewart-x1t 9 дней назад

I have a similar problem with a CSV file, there are title characters before the Column headings. How could I remove those?

@ajay249 11 месяцев назад

Goodly is just too Godly.

@devendrareddynamballa1053 2 месяца назад

What if we want to add back the removed rows after promoting the headers

@babakmohammadi9931 9 месяцев назад

could you possibly tell me what do i have to do with CSV files for "Table.TransformColumns(Source, {"Content", Excel.Workbook})" as that dosent work for CSV

@williamarthur4801 Год назад

This may sound like a stupid question, and I'm sure it's something basic, but why do you get Name = Sheet , Data = Table, but I always have Name= table , Name = Sheet and adjacent Data = table, Data = table ? Oh, and loved the use of a condition for skip which I'd never thought of, even though now having looked it does say count or condition.

@eslamfahmy87 11 месяцев назад

If the name & kind of data which extracted into PQ it's inconsistent and i need to filter out for all non needed sheets, How can work with that sheet

@huyuc6614 6 месяцев назад

Hi i stuck on the step transform column. My file is .csv file not xlsx so when i use transformcolumn, it show error on the content. Do you know how to fix it

@oOBlindyOo 3 дня назад

My source data is from pdf. Not excel. So the formula '=Table.TransformColumns(Source,{"Content", Excel.Workbook}) isn't working... any ideas?

@markp8600 Год назад

Fabulous video , but it is difficult to see the "Applied Steps" at the end of the video. Question: what if the junk rows and "junk row data" are spread inconsistently through the spreadsheet. For example, the fund or department information may change resulting in blank rows between datasets and header rows when a new fund source or department is identified in the report. The example shows how to remove junk rows at the top of the report for 3 reports. Do you have any videos where the junk rows would be sporadically located throughout the report?

@GoodlyChandeep Год назад

Mark, I'll have to take a look at the data to give you possible ways to solving it. See if you can pick any tricks from this long video - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-_ZKT1raC4P0.html I've shared horizontal and vertical looping techniques in this video.

@markp8600 Год назад

@@GoodlyChandeep - Hi Chandeep, I could share a file with you on your website. I could upload on the PQ training course site. Would that work? Thanks for the great insights!

@markp8600 Год назад

@@GoodlyChandeep Perfect! Thank you so much Chandeep. Your help and guidance is greatly appreciated. Cheers!

@PandaVlademirBerlin 2 месяца назад

What if i need the source.file column?

@cdmkk2211 5 месяцев назад

How can I add files name as a column in this

@boissierepascal5755 Год назад

Power query is magic ! You are a wonderful magician 🪄

@ankitachauhan5834 Месяц назад

How to do same thing but fro CSV file

@carolshipley7903 27 дней назад

Hi. Can you give me away of doing this if there is more than one worksheet in the excel file and you only want to clean just one worksheet?

@GoodlyChandeep 27 дней назад

If you're connecting to a single xl file, you'll have to apply a filter before the Navigation Step to restrict the sheet names that you want.

@haroldmadrigal3671 3 месяца назад

it fails when there is #ref or formulas(lookup) in the junk rows

@deepraazshaw7369 Год назад

Sir, can we get all these videos in Hindi?

@abhiadfree6331 10 месяцев назад

Lets name you the M-man...

@excel-k-sir 11 месяцев назад

Bhai I am sure you have definitely made your hands dirty in cleaning up the Consolidated Account Statement for MF. Can you please create a video on how to clean up the Statement.

@user-ni9sj8gv2o Год назад

Hi Chandeep, i want to enroll your course about Dax and Data Modelling (Bundle), can you give me some discount? hehe it's too expensive for me :(

@utkur765 Год назад

Why not to use 2 column and filter out "null"? This will remove all just rows from all files.

@GoodlyChandeep Год назад

Column 2 can have legit null values

@DJPejsen Год назад

Source = Folder.Files("C: \Users\pc\Desktop \Remove Top Rows"), ContentTbl = Table.TransformColumns( Source, { "Content", each Excel.Workbook(_) } ), ExpandData = Table.ExpandTableColumn( ContentTbl, "Content", {"Data"}, {"Data"} ), TableSkipRows = Table.PromoteHeaders( Table.TransformColumns( ExpandData, { "Data", each Table.Skip(_, each [Column1] "Date" or [Column5] "Profit")) } ) )