Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
You can select the column dropdown and choose "Remove Empty":
Instead of selecting "Remove Empty", unselect the values that appear to be blank. For more information, take a look at Advanced Editor.
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.
You can select the column dropdown and choose "Remove Empty":
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?