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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

24 REPLIES 24

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.

so much easier , thank you!

 

MarcelBeug
Community Champion
Community Champion

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.

 

Specializing in Power Query Formula Language (M)

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.