Forum Discussion
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 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
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
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 _})
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
25 Replies
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
Thanks!! I was just leaving the box empty; now that I've written 'null', it works!!
This worked great for me in 2020.
using only the word: null
Much appreciated
I can confirm that using the word null does the trick.
This also works for replace 'error' values as well. Click the drop down and it shows up.
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 _})
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!!!
How can I apply the same technic if the data type is decimal? Thank you
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?
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
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
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.
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.
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
Anonymous that would be a new topic for you to create.
