"Remove blank rows" doesn't seem to be working, and other strange behaviour

[callum]

Hi,

I have a dataset where one column has some occasional blank values, and I'd like to remove the corresponding rows from the dataset. In the query editor, I select the column so it's highlighted, and go to Remove rows>Remove blank rows at the top. However, this doesn't seem to remove the rows in question.

I thought perhaps the values were a single space, for example, rather than an empty string, and this is why they're not being removed. So in DAX I added another column, "length", with the value of LEN(tablename[columnname]). But the weird thing is, in the rows where columnname is "blank", so is the length! I don't see what could be causing this, as even LEN("") returns 0, and I can't think of any other inputs that would return nothing when put into the LEN function.

So firstly, am I doing something wrong in trying to remove rows with an "empty" value in a certain column, and secondly, how come the calculated column using LEN does not have any value whatsoever on these rows?

Thank you.

[v-chuncz-msft]

@callum,

Instead of selecting "Remove Empty", unselect the values that appear to be blank. For more information, take a look at Advanced Editor.

[MarcelBeug]

So if you want to remove rows that are completely empty (in all columns), you can use the option "Remove Rows" - "Remove Blank Rows", which will generate code like from you can see that it checks all fieldvalues in each entire row:

= Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

If you want to remove rows with an empty value in e.g. Column2, my suggestion in my previous post generates code like:

= Table.SelectRows(Source, each [Column2] <> null and [Column2] <> "")

@v-chuncz-msft if I understand correctly, you are from Microsoft, in which case I think it's rather strange to advise not to use the standard option. That would be a good advice if this functionality is used, and still some "empty" values would still remain.

But as far as I understand, the case here is that rows with empty values in a column must be removed and not rows that are completely blank.

Then again: possibly I misunderstood and your post will still be marked as solution.

[MarcelBeug · Accepted solution]

You can select the column dropdown and choose "Remove Empty":

[Greg_Deckler]

Try creating the equivalent Power Query custom column with something like Text.Length

https://msdn.microsoft.com/en-us/library/mt253345.aspx

See what it returns and then maybe you can filter out all the zeros or ones on this column?