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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi MarcelBug

 

Please can you give the steps required to do this please.

 

I have a date type but would like to display a string message if it is NULL. Would that be possible?

 

Thanks

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I have same problem whit datetime column, nothing work for date.

I was able to resolve the error using the information I got from the site https://blog.learningtree.com/error-handling-power-query/

Expand the column error and insert another column using the information in this column.

Aply this:

try
(if [DATA PARA DEVOLUÇÃO] >= (DateTime.Date(DateTime.LocalNow())) and [DATA DA DEVOLUÇÃO] = null
then "No Prazo"
else if [DATA PARA DEVOLUÇÃO]<= (DateTime.Date(DateTime.LocalNow())) and [DATA DA DEVOLUÇÃO] = null
then "Atrasado"
else "Devolvido")

Customize expanded (has an icon to the right in the column heading, click there) and insert this:

if [Personalizar.Value]= "Atrasado" then "Atrasado"
else if [Personalizar.Value] ="Devolvido" then "Devolvido"
else "No Prazo"

good luck!!!