Forum Discussion
how to define null values
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.
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 !!
2 Replies
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]
)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 !!
