Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
COMtrac
Advocate I
Advocate I

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 

3 ACCEPTED SOLUTIONS
Baskar
Resident Rockstar
Resident Rockstar

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 

View solution in original post

MarcelBeug
Community Champion
Community Champion

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

Specializing in Power Query Formula Language (M)

View solution in original post

v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @COMtrac,

 

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

 

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

 

c1.PNG

 

 

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

 

r1.PNGr2.PNGr3.PNG

 

Regards

View solution in original post

25 REPLIES 25
Anonymous
Not applicable

It should be right DAX with closing bracket.

 

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

 

pjandliz
Advocate II
Advocate II

This ended up working for me...

 

= Table.ReplaceValue(#"Add Client name", null, "Not found in Client table", Replacer.ReplaceValue, {"vcClientName"})

 

Note, null is without quotes. 

Anonymous
Not applicable

none of the below mentioned solutions are working for me. I am trying to change the null values from a column whose data type is date.

@Anonymous that would be a new topic for you to create.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

None of the mentioned solutions are working for me. I am trying to replace null from a column whose format is date and it is not working.

v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @COMtrac,

 

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

 

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

 

c1.PNG

 

 

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

 

r1.PNGr2.PNGr3.PNG

 

Regards

Thanks

This also works for replace 'error' values as well. Click the drop down and it shows up.

This worked great for me in 2020.

 

using only the word: null

 

Much appreciated

AliceW
Impactful Individual
Impactful Individual

 I can confirm that using the word null does the trick.

AliceW
Impactful Individual
Impactful Individual

Thanks!! I was just leaving the box empty; now that I've written 'null', it works!!

MarcelBeug
Community Champion
Community Champion

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

Specializing in Power Query Formula Language (M)

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?

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

Anonymous
Not applicable

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
Not applicable

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

Baskar
Resident Rockstar
Resident Rockstar

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 

Anonymous
Not applicable

THX!

Anonymous
Not applicable

Hi,

 

Does this work in a Custom Column in Direct Query Mode? I keep getting the error "Expression.Error: The name 'isblank' wasn't recognized.  Make sure it's spelled correctly." Please advise.

Anonymous
Not applicable

Hi,

 

Does this work in a Custom Column in Direct Query Mode? I keep getting the error "Expression.Error: The name 'isblank' wasn't recognized.  Make sure it's spelled correctly." Please advise.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.