Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
Solved! Go to 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")
)
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!
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!
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")
)
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |