Latest Enterprise DNA Initiatives

Identify all Blank rows

I have a few thousand rows and dozens of columns and I want to filter out or preferably delete all completely blank rows. How can I know for certain, in the Power Query Editor, that the blanks I filter out or delete has each and every column blank? I don’t want to lose any data.

You can do this quite easily I think and I would be confident that the query editor gets it right in terms of deleting all of the blank only rows.

One way you could test it would be to bring the entire table in first and run a calculation, see what results you get, and then compare those to the now filtered table after you’ve done that filtering in the query editor.

Thanks
Sam

So, if I understand it correctly, after filtering out the blanks using any column, the Query Editor would only “delete” all rows where each and every column is blank, is that correct? To double check, I could do the calculation you suggest?

You could test for a completely blank row in Power Query, the M code below will add a column to your table and return the value TRUE for a blank row.

Table.AddColumn(YourPreviousStepName, "EmptyRow", each List.IsEmpty( List.RemoveNulls( Record.ToList(_))), type logical)

No risk of data loss :smiley:

Thanks, Melissa!

I’ve applied the code you provided but it returns false and does not remove any empty rows.

I’m not very familiar with M code but it looks like this is only trying to remove nulls and not empty strings? The table has both numbers and text. The nulls seem to be present in the numeric cells and textual cells have blanks.

I’m guessing that the M code needs to account for the text blanks as well? I tried a few adjustments to the code but I am not very familiar with M code yet.

Please supply a small sample, thanks

Yes, it looks like the problem is that the .csv file treats the blanks differently than a .xslx file. I simply saved the .csv file as a .xslx file and your code works, but what to do with a large number of .csv files with the same issue!

I will get the sample to you soon.

Hi Melissa,

I’ve attached a sample source, pbix, and some screenshots.
Sample_1.zip (228.9 KB)

Thanks so much!

@powerbideveloper

I found an article you might find interesting…

Added a List.ReplaceValue to the first solution and that seems to do the trick.

So here’s the new M code that returns a TRUE value for a blank row.
Table.AddColumn(YourPreviousStep, "EmptyRow", each List.IsEmpty( List.RemoveNulls( List.ReplaceValue( Record.ToList(_), "", null, Replacer.ReplaceValue))), type logical)

and here’s your file
Sample_1.pbix (58.6 KB)

1 Like

Melissa,

Wow, this is very helpful and thanks so much for taking the time!