Replace NULL

[COMtrac]

I am sure this is something simple I am missing but I can't for like of me solve it.

i have table called INCIDENTS with a column in the table called PROVINCE. within that column there are Multiple entries of Province A , Province B, Province C and so on but it also has a number of entries of NULL.

Objective: Replace all cells with "null" to "unspecified"

i followed to introduction video ( I think it was video 1-4 or 1-5 where it Demonstrates how to create a new column in query editor and replace NULL with USA then delete old column that had null and keep new column.

I followed these instructions exactly (except for USA of course) but new column still has NULL

The query I used was = if 'Incidents' [Province] = null then "Unspecifed" else 'Incidents' [Province]

the above is exact text I typed for query

however null still remains in new column..... Am I missing something 

[Baskar · Accepted solution]

Try this one dude.

Create new calculated column using DAX.

Column =     if (   isblank( 'Incidents' [Province]),  "Unspecifed" ,   'Incidents' [Province])

This willhelp u if not let me know i will help u 

[MarcelBeug · Accepted solution]

Alternatively in Power Query you can use Table.TransformColumns, so you don't need a new coumn:

= Table.TransformColumns(Source, {"Province", each if _ is null then "unspecified" else _})

[dineshkumar_vrv]

hi I want to fill the missing value as "Blanks" in the data so that I can display the list in slicer with one line item as Blanks, so how to fill the blanks with the word "Blanks" 

Thank you in advance 

[Gazzer]

I was having the same problem as I think you are having.

The issue seems to be that the text version of "null" is lowercase.

Here is what I did:

• Use Format option to set the column values to UPPERCASE
• Then use Replace Values to change NULL to null

It treats NULL as being text, but null as being an actual null value.

[Sunnie]

so much easier , thank you!

[MarcelBeug]

Try and replace = null with: is null

Edit:

Explanation: all values in Power Query are classified by a type.

Not only data types, but for instance also a table has a table type (which is actually the collection of table fields, their data types, any key specifications and any metadata).

With "= null" you compare a value with the value null, which is not possible.

With "is null" you check if a value is of type null.

[COMtrac]

Thank you folks for your prompt assistance Sorry for late response... Christmas holidays and all... I will try these solutions and give feedback.  I'm not sure if it make a difference but I am working in Power BI desktop andnot PowerBI service