Removing Blank Rows in Excel - 2 DataSets, 4 different approaches and a clear winner - Power Query

This video will teach you how to remove blank rows from your data, which we all have to do from time to time. There are two stages to the problem—only Blank Cells and then Entire Blank Row. And there are even more stages to the Solution. Four all together. The GoTo special Command, basic filtering, TEXTJOIN function, and the grand finale, the Power Query Solution. You will notice that (once again) Power Query turns out to be the perfect tool for the job. It's another one of those "I wish these were built into Excel" tools in Power Query, and it just works perfectly.

This tutorial was made using Excel 365, but most methods work with Excel 2010 and above. The exception being the TEXTJOIN function which is only available since Excel 2019.

Chapters:
0:00​ Excel Olympics Animation
0:08 Introduction to Blank Cells and Blank Rows
1:02 Solution 1 - Simple Table - GoTo Special Solution
2:05 Solution 2 - Simple Table - Filter Solution
3:28 Solution 3 - Complicated Table - The TEXTJOIN solution
5:49 Solution 4 - Complicated Table - Power Query solution
7:24 Outro

Gear Used:

US Links

EU Links



DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links that I provide I may receive a small commission with NO additional charge to you! Thank you for your support!

#Excel #ExcelOlympics #gasperkamensek

[abetterfuture]

Awesome trick with that TextJoin. Just saved me a few hours of hand-cramping BS my dude so thank you!

[wayneedmondson]

Hi Gasper. Great tip! Another cool option in Power Query is if you also have blank columns that you want to delete, you can first delete blank rows, then transpose, then delete blank rows again, then transpose again. The only problem with this is you lose the column names. If you want to keep the original column names, you can load the table, add an index column, unpivot other columns from index column, in this step.. Power Query automatically removes blank cells, then choose the Attribute column, pivot on Value / Don't Aggregate and then delete the index column.. close and load. This procedure will preserve the original column names.. less the blank columns (and rows). Power Query is full of surprises. Love learning new things about it. Thanks for sharing :)) Thumbs up!!

[IssueBoyStefan]

Another handy one. I believe that instead of textjoin we can use also counta and filter the rows with zeros. However this will work only with true blanks (nulls) and not with empty strings. :)
Thanks, Gasper!