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

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

  • 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!!!

  • nahomzw's avatar
    nahomzw
    Frequent Visitor
    6 years ago

    How can I apply the same technic if the data type is decimal? Thank you

  • evahere's avatar
    evahere
    New Member
    2 years ago

    Lovely! I'd love to  not have to create a new column. But PBI won't let me mix types (column is decimal number). Any solution for this? Only DAX/custom measures in PBI?