Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |