Handling NULL values

[Vickram]

I have seen many posts and solution re handling NULL values in Power BI but I could not find the solution I am seeking. 

I have a column with variant data type: Number and NULL. So, this data cannot dispalyed in bar chart or column chart etc. So, I replaced NULL with 0, now all visualization works perfectly fine.

My next requirement is to check if the values are NULL then exclude it from the IF condition. I cannot use the previously replaced 0 values in my IF condition because some of the values for this column is genuinely 0. So, if I exclude all 0s, then the actual column value of 0 will also be excluded in the IF condition, this is not what I want.

Any solution to this? If this type of issue has already been discussed and resolved please point me to that direction.

Thanks.

[Ashish_Mathur · Accepted solution]

Hi,

The Data type of the column should be decimal and you should not feel the need to replace null with 0.  Your visuals should work perfectly.  Can you share the file's download link and show the exact problem.

[Vickram]

Thanks heaps Ashish. When data type converted to Decimal, yes the visualization works fine even with the NULL values 

Little bit curious about below:

• The column has NULL values in SQL server database which I extracted to Excel table (as the machine I am working on does not have SQL Server DB connection). So, this Excel table is the source of my power BI.
• When data is exported from the Excel, Power Query Editor shows the null values as NULL
• When data type is converted to decimal, the null values becomes null in Power Query Editor window
• When "Close and Apply", the table in DAX or main PowerBI windows, shows the null values as blank
• The same column also contains values which are 0s (zero). So,the column now contaions number values, 0 and blank values.
• Now, while using that column in IF condition, 0s are also treated as being blank

This is bit strange to me. Anyway, I found a way to deal with 0 being treated as blank by using ISBLANK() function. So, all good. If you can shed some light why Power BI treats NULL as explained above, would be great.

Thanks again Ashish.

[Ashish_Mathur]

You are welcome.  In the Query Editor, Null is treated as blank.

[FarhanAhmed]

Keep your null values as is in the table and you can create a measure like this to handle NULL Values

_Test = CALCULATE(IF(SUM('Table'[Column2])=BLANK(),0,SUM('Table'[Column2])))

[Vickram]

It did not work. Throws below error when tried to display the measure in a key card.

[Vickram]

Also, checking null values in IF condtion as below does not work

e.g. =IF([colA]=null, 0, [colA]). Can we check null using IS NULL like =IF([colA] IS NULL, 0, [colA]) just like in SQL.

Not sure how to check if the value is NULL in Power BI:

[colA]=null

or

[colA] is null

or

[colA] ="NULL"

or

[colA]="null"

or

[colA]=BLANK()