How to replace blanks with a different value when connecting to a table

[paguy215]

I have an excel file that lists various companies and a category they fall into ... IE Company A is Elite, Company B is Value, etc

I have two other tables with data that include a company code field, that links to the same code in that Excel file.  However, the issue I am having is there are a lot of company codes that are not in the excel file.   Currently, Power BI is returning blank for those.   Ideally, I would like it to return 'Other' ...is there an easy way to do this?  I can't add all company codes to the Excel file and create 'Other' in the category column because new codes are added every day

[AnnieTay · Accepted solution]

Assuming that Table1 is your Excel file, and Table2 contains the codes (ID) that are not present in Table1, you can add a calculated column to Table2: 

ID_clean =
IF(
ISBLANK(
LOOKUPVALUE(
Table1[ID],
Table1[ID],
Table2[ID]
)
),
"Other", 
FORMAT(Table2[ID], "General Number") 
)

[Anonymous · Accepted solution]

Thanks for the reply from lbendlin and AnnieTay.

Hi @paguy215 ,

Have you solved your problem? Based on my testing, the code provided by AnnieTay does what you need:

This is the relationship I created:

You can also use the RELATED function, which does the same thing and doesn't need to have the fields in the Table:

Column = IF(ISBLANK(RELATED('Table'[Category])),"Other",RELATED('Table'[Category]))

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[AnnieTay · Accepted solution]

Assuming that Table1 is your Excel file, and Table2 contains the codes (ID) that are not present in Table1, you can add a calculated column to Table2: 

ID_clean =
IF(
ISBLANK(
LOOKUPVALUE(
Table1[ID],
Table1[ID],
Table2[ID]
)
),
"Other", 
FORMAT(Table2[ID], "General Number") 
)

[lbendlin]

COALESCE([column],"Other")

[paguy215]

I couldn't quite get that to work...where should I be adding that measure?

[lbendlin]

does it have to be a measure? A calculated column is likely enough.

[paguy215]

it doesn't have to be a measure, but where would I put the calculated column?  would i have to add it to both tables that are joined to the Excel file?  I already have that field in there, but I would like to have a filter tie to both tables