Remove Blank Rows/Columns with Power Query Incl. STUBBORN Characters!

Non-printing characters, spaces and empty strings can make it difficult to remove blank rows and columns. This video shows two approaches ans shows you how to create a custom function you can use to automate the process.
Download the example file here: www.myonlinetraininghub.com/R...
Learn Power Query with Mynda here: www.myonlinetraininghub.com/e...
Follow Mynda on LinkedIn: / myndatreacy
0:00 Introduction
0:25 Example Data
0:41 Removing blank rows & columns using unpivot
2:59 Formula to automatically remove blank rows
7:38 Formula to automatically remove blank columns
12:16 Function to automatically remove blank rows

[@hectorbello2104]

I was dealing with this exact issue a few months ago in one of my assignments at work. It took me ages to remove empty columns, but I never got a clean solution as the one here explained. I will certainly be using this function from now on. thanks for sharing.

[@MyOnlineTrainingHub]

Great to hear you'll be able to make use of it in future, Hector 😊

[@w13ken]

Thanks Mynda and Phil, there were some really useful PQ functions that were new to me here so I will keep this video on speed dial. Also really helpful to see how to create a repeatable function. Keep up the awesome work!

[@MyOnlineTrainingHub]

Our pleasure! 😊

[@10ozGold]

Brilliant Phil and Mynda!!! So good, super useful. Thank-you so much!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@TheOctophant]

Oh crap. I needed that Video like three weeks ago. I was struggling so bad with this

[@MyOnlineTrainingHub]

Damn! Better late than never 😉

[@IvanCortinas_ES]

Excellent tutorial. Thank you Mynda. That's very complete!

[@MyOnlineTrainingHub]

Glad you like it, Ivan!

[@darrylmorgan]

Hi Mynda!Another Excellent And Very Helpful Tutorial From Phil...Thank You Both :):)

[@MyOnlineTrainingHub]

Glad you enjoyed it, Darryl!

[@wayneedmondson1065]

Great lesson! That function is a mind bender. Thanks for sharing the code. Thumbs up!!

[@MyOnlineTrainingHub]

Cheers, Wayne!

[@chrism9037]

Excellent, thanks Mynda. PQ can do just about anything!

[@MyOnlineTrainingHub]

So true, Chris!

[@iankr]

Wow! Many thanks, Mynda & Phil. That's pretty heavy. I will need to have a look at that code more closely (split onto separate rows) to see what's going on! 😀

[@MyOnlineTrainingHub]

Cheers, Ian!

[@hamishadamson4628]

This is fantastic! Just starting my power query journey. It does make me wonder why this isn't a default behaviour/option. It seems like the power query team did half a job regarding this.

[@MyOnlineTrainingHub]

Glad it was helpful, Hamish. Power Query is still being developed, so maybe that functionality will come later.

[@kebincui]

This is an awesome video, thanks to Phil and Mynda👍🌹. By the way, the step of removing blank row step can be made a little bit shorter by trimming and cleaning first and then removing "" and null. This is reduce the step of just removing "", as below: = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(List.Transform(Record.FieldValues(_), each try Text.Clean(Text.Trim(_)) otherwise _), {"",null})))

[@MyOnlineTrainingHub]

That's a great idea! Thanks for sharing 🙏

[@saadnageeb4633]

Wonderful , Thanks very much

[@MyOnlineTrainingHub]

Welcome!

[@paulcollins841]

Very good and very well explained

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@GeertDelmulle]

Bravo! This is a golden nugget! Good job! :-)

[@GeertDelmulle]

PS: the logic applied reminds me of how we filter in DAX measures.

[@MyOnlineTrainingHub]

Thank you! 😃

[@GeertDelmulle]

@@MyOnlineTrainingHub Hey Mynda & Phil, I've simplified the code a little bit. Do you see the analogy between step 2 and 3? What do you think about it? Here it is: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], RemoveBlankRows = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(List.Transform(Record.FieldValues(_), each try Text.Clean(Text.Trim(_)) otherwise _),{"",null}))), RemoveBlankCols = Table.SelectColumns(RemoveBlankRows,List.Select(Table.ColumnNames(RemoveBlankRows), each not List.IsEmpty(List.RemoveMatchingItems(List.Transform(Table.Column(RemoveBlankRows,_), each try Text.Clean(Text.Trim(_)) otherwise _),{"",null})))) in RemoveBlankCols

[@genechicago4806]

Wow, that's an impressive formula! I started using Power Query a few months ago thanks to your videos, and this is something that I've come across often. Thank you! Question, is there a way to remove duplicate records in Power Query using multiple columns similar to the Remove Duplicates button in Excel? I've come across instances where removing duplicates using only one column doesn't yield the right answer.

[@MyOnlineTrainingHub]

Sure is, Gene. Just select the columns you want considered for duplicates before clicking on the 'remove duplicates' button.

[@farmerleigh6361]

Where do I place the fxRemoveBlanks function within the code when I import my data from a different excel file? I have a Source and Navigation step with this code: let Source = Excel.Workbook(File.Contents("B:\Monthly Reports Test Files\Source Data Example.xlsx"), null, true), #"Source Data Example_Sheet" = Source{[Item="Source Data Example",Kind="Sheet"]}[Data] in #"Source Data Example_Sheet"

[@JennTheWriter]

This is good and I will have to go through it several times for Excel. One question I have is that, once this is done, and you want to put it into Power BI, will the data transfer as already cleaned, or do you have to do all the cleaning over again? The cleaning part of data is really important and I need to know all of it for both Excel and Power BI, as I see it.

[@JennTheWriter]

I don't suppose you have a book written specifically on cleaning data for both programs? LOL!

[@MyOnlineTrainingHub]

The process is exactly the same in Power BI. Both tools use the same Power Query engine 😉

[@JennTheWriter]

@@MyOnlineTrainingHub , thank you for the response. :)

[@RonDavidowicz]

I need a nap now!

[@MyOnlineTrainingHub]

😁 it's definitely a multi-view tutorial.

[@danielnoce75]

This is great. I’m curious if it’s possible to do something similar for #NUM! Errors?

[@philiptreacy714]

Thanks Daniel. If you have a #NUM error, when you load that to PQ it'll show as Error in the column(s). You can select the column(s) or select the entire table (CTRL+A) then right click on a column header and then on Remove Errors. Alternatively, you can check the Error and see what type it is, then do something accordingly. Please check this post to see what I mean www.myonlinetraininghub.com/iferror-in-power-query-using-try-otherwise

[@othmanashara9382]

is there another way more easier?

[@MyOnlineTrainingHub]

You can try using filter to remove blanks.

[@othmanashara9382]

@@MyOnlineTrainingHub yes, i did it with filter and save a lot of time. Thx

[@dp70737]

Why wouldn’t you just select all and transform>clean, transform>trim to the whole thing and then just use the pivot trick?

[@philiptreacy714]

Hi Derrick. Trim and Clean result in empty strings so the Unpivot/Pivot trick won't remove those rows/columns containing empty strings

[@nimeshkotadia6526]

How not to remove blank columns from the table? Instead keep it as it is. I am facing problems while stacking tables because power query removes blank columns from tables on it's own when i want them to be present in the orignal tables which are read into powerquery.

[@MyOnlineTrainingHub]

As long as the column has a header, Power Query will bring it in.

[@rrrraaaacccc80]

💯👍

[@MyOnlineTrainingHub]

You're welcome 😊

[@martinargimon730]

Hi Phil, sorry but your explanation is for advanced users ( i must assume ) since you immediately ( at the start of your video) you talk about "indexing" columns, pivoting etc, etc,.. So it is not really helping us.. I know you are an expert but we are not,. Perhaps a bit more detail explanation will help us better ?.. I've been following Mynda's channel all the time, she is definitively great, but your explanations lack the required 'additional tips' (details) to get thru' these more 'involved' features. Just a constructive criticism,.. Hope you don't mind . Thanks. martin

[@MyOnlineTrainingHub]

Thanks for the feedback, Martin. Unfortunately, every video cannot start with the basics. If you want to learn Power Query then we recommend a structured approach by taking a course (www.myonlinetraininghub.com/excel-power-query-course). Our KZbin channel is a place to share ideas which can be pitched at any level. We understand that not all videos will appeal to all people. That said, perhaps we could have done more to explain the basics and will consider that in future.

[@martinargimon730]

@@MyOnlineTrainingHub indeed Mynda. I should have checked your other videos before posting my comment. Sorry about this. Thanks again. Kind regards

[@hamishadamson4628]

Just copy the steps exactly, or download the file and grab it from there. You can use it without necessarily understanding it.

[@svitovidable]

too complicated:(

[@MyOnlineTrainingHub]

If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@user-db1ux4yi6l]

I gave up following the steps. Too complicated to follow.

[@user-bp7yu8vk3f]

very confusing explanation .i just need to remove blank rows .

[@MyOnlineTrainingHub]

You can do that using the 'Remove Rows' tool on the Home tab of the Power Query Ribbon.

[@KentLundgrenLarsErik]

Thanks for: = (Source as table) => let #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(List.Transform(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}), each try Text.Clean(Text.Trim(_)) otherwise _ ) , {""}))), #"Removed Blank Cols" = Table.SelectColumns(#"Removed Blank Rows" , List.Select(Table.ColumnNames(#"Removed Blank Rows"), each List.NonNullCount(List.Transform(Table.Column( #"Removed Blank Rows", _) , each try if Text.Clean(Text.Trim(_)) = "" then null else _ otherwise _ ) ) > 0 )) in #"Removed Blank Cols" I would never have fixed that myself!

[@MyOnlineTrainingHub]

Glad it was helpful 😊