Latest Enterprise DNA Initiatives

Checking for blank values

Query Editor
#1

Hi there,

I am calculating a column in Power BI. I am checking if another column value is blank by using if condition, for example:
if [column_name] = null then perform some operation
else some other operation.

However, when I hit enter, it throws an error. When I looked into the error I found that another column value is blank and does not have a ‘null’ value. Is there any other way to check the blank value in power query?

#2

Two questions:

  1. What’s the whole formula you are trying to use?
  2. What exactly does the error say? Can you take a screenshot of it for us?

If you can upload a sample pbix file for us to look a that would help us see exactly what is happening and how to fix it.

#3

Hi @leo_89,

null is the absence of a value but you can also have blank ‘cells’ that can be any number of things like an empty text string (2 double quote signs), a space or you name it… try something like this.

if List.Contains( {null, “”}, [column_name] ) then perform some operation else some other operation

I hope this is helpful

2 Likes
#4

I was most definitely thinking in dax but this error definitely makes way more sense in PQ. I was so confused how this error would happen in dax haha

@leo_89, @Melissais right. This is why if you click on the filters and select “remove empty” you’ll see that it filters out both null and “” and not simply one or the other. One simple solution to your problem would be before adding your conditional column, to select the column and click replace values and for the value to replace from put “” and for the value to replace to put null. I’d personally also perform a clean transformation on the column before that just to make sure there’s no white space or nonprintable text hiding in the column.

That all being said, I would defer to whatever @Melissa says before when it comes to Power Query. I’d honestly follow her advice on anything Power Query haha

1 Like
#5

Thanks @Melissa. I totally agreed with you (@bradsmith). We should either remove null values or create a new column that addresses null-related issues. I have already got around 50ish columns in my fact table (mostly coming from dimension tables). So I am bit reluctant to create new columns in the fact table. If you have any advice on hiding/reducing no of columns then I would be very happy to listen.