how to define null values

[Anonymous]

Hi,

i was trying to define the null values as "Undefined" but my formulas arent working. what is the best approach for this?

Most of the columns are from one table. Region New Count ECM and Facility Key are from the same table.

Facility Table = COUNTRYNAME, FACILITYDES, FACILITYKEY

Fact Table = Region New, Count ECM, Facility Key

Wanted to Get this result:

Thank you.

[anmolmalviya05 · Accepted solution]

Hi @Anonymous,

There are various ways to deal with this: 
First, as mentioned by @bhanu_gautam, you can create calculated column using below approach and then use this into your visualisation

NewRegion = IF(ISBLANK(FactTable[Region New]), "Undefined", FactTable[Region New])
NewCountECM = IF(ISBLANK(FactTable[Count ECM]), "Undefined", FactTable[Count ECM])
NewFacilityKey = IF(ISBLANK(FactTable[Facility Key]), "Undefined", FactTable[Facility Key])

Note: using calculated columns can increase the size of your report because they add additional data to your data model. Each calculated column is stored in memory, and if you have large datasets, this can significantly impact the size and performance of your Power BI report.

Alternatively you can replace the null values with "Undefined"/"Unknown" from power query editor.

• Open the Power Query Editor.
• Select the column you want to modify.
• Go to the "Transform" tab.
• Select "Replace Values" and enter null in the "Value to Find" field and "Undefined" in the "Replace With" field.
• Click "OK".

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[bhanu_gautam]

@Anonymous , You can create new calculated column using ISBLANK to replace null value with whatever you want

COUNTRYNAME_Cleaned =
IF(
ISBLANK(FacilityTable[COUNTRYNAME]),
"Undefined",
FacilityTable[COUNTRYNAME]
)