Replacing Blanks with text "Unmapped"

[Anonymous]

Hi,

I want to replace Blanks with text "Unmapped".Itried using  "ISBLANK'  function , however I was not able to replace the Blank value with the text as the Dim column is not having any blank values and the Blank which I am seeing in my PBI visual is due to Joining Dim with tables. 

Thank you in Advance

[Vickar]

create a calculated column in the fact table with the below code

PIN = 
var _pin = RELATED('tablename'[PIN])
return
IF(NOT(ISBLANK(_pin)),_pin,"Unmapped")

and replace the PIN column from dim to PIN column from fact  in the required visuals. this would replace the "blanks" with "unmapped"

[amitchandak]

@Anonymous 

In Edit Query/Data transformation mode you have a Replace values option. You can use that.

https://support.microsoft.com/en-ie/office/replace-values-power-query-28256517-f1e9-4dc3-832f-45786e...

In DAX

New column = if(isblank([Column]),"Unmapped",[column])

from March 2020, you can have

Coalesce([column],"Unmapped")

Also refer:https://www.syntelli.com/easy-guide-to-bulk-replace-values-in-power-bi-or-power-query

[Anonymous]

Hi Amit,

Thank you 

I have already tried Coalesce & isblank function , however I was not able to replace the Blank value.

 Replace values option : I do not see any blank value in the column.Blank value is only comming after joining the Dimension table with fact tables.

[amitchandak]

Ideally, you should not get blank from the dimension side.Are they coming from dimensions? If so make sure your dimension has all the values

 In a case coming from fact.

for the measure, you can do

+0

or

& "Unmapped"

Depending on the data type

[Anonymous]

Hi Amit,

There are no blank values in Dim column(PIN) .I guess those are the records comming from fact table for which there is no pin assigned in the dimension table.So we have to display those blank values as "Unmapped" in report .

PIN	EHC	EHire
	53	40
Admin	32	50
Architecture	30	20

We need to display as below:

PIN	EHC	EHire
Unmapped	53	40
Admin	32	50
Architecture	30	20

[Vickar]

create a calculated column in the fact table with the below code

PIN = 
var _pin = RELATED('tablename'[PIN])
return
IF(NOT(ISBLANK(_pin)),_pin,"Unmapped")

and replace the PIN column from dim to PIN column from fact  in the required visuals. this would replace the "blanks" with "unmapped"

[Anonymous]

HI @Anonymous,

This blank category is generated by your relationship filed and not really existed in your table, I'd like to suggest you create a table to extract all categories add 'Unmapped' category to it. 

NEW Table =
UNION ( VALUES ( Table[Pin] ), ROW ( "Pin", "Unmapped" ) )

After these, you can use it as the category of the table and write measure formulas to lookup corresponding records. 

In addition, can you please share some dummy data with a similar data structure to test? It will help to test and coding formulas on it. 

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng