Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

  • AnnieTay's avatar
    AnnieTay
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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!

6 Replies

  • AnnieTay's avatar
    AnnieTay
    Frequent Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

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

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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!