Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
paguy215
Helper III
Helper III

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

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

2 ACCEPTED SOLUTIONS
AnnieTay
Frequent Visitor

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

View solution in original post

Anonymous
Not applicable

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:

vlinhuizhmsft_0-1728367831956.png

This is the relationship I created:

vlinhuizhmsft_1-1728367881704.png

 

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

vlinhuizhmsft_2-1728368188896.png

 

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!

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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:

vlinhuizhmsft_0-1728367831956.png

This is the relationship I created:

vlinhuizhmsft_1-1728367881704.png

 

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

vlinhuizhmsft_2-1728368188896.png

 

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
Frequent Visitor

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
Super User
Super User

COALESCE([column],"Other")

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

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

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 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.