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

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") 
)

Thanks for the reply from lbendlin and AnnieTay.

Hi Anonymous ,

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!