Forum Discussion

COMtrac's avatar
COMtrac
Advocate I
9 years ago
Solved

Replace NULL

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 e...
Need Help
  • Baskar's avatar
    Baskar
    9 years ago

    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's avatar
    MarcelBeug
    9 years ago

    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 _})

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi COMtrac,

     

    In addition, have you tried the "Replace Values" option within Query Editor? It should also work.:smileyhappy:

     

    1. Select the cell value you want to change(select null in this case) in Query Editor.

     

     

     

    2. Click "Replace Values" option under Home tab. Then you should be able to change null to "Unspecified" like below.

     

     

    Regards

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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's avatar
COMtrac
Advocate I
9 years ago

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