Power BI Desktop: Why does my mapping table not work for empty cells?

[Anonymous]

Hello!

I have Data about employees with a status abbreviation that can either be "e", "m" or empty and I want to map that status with a different translation table that maps "e" onto "Employee", "m" onto "Manager" and empty cells onto "Unemployed".

I have a data table source that is a CSV-file and if "status" is empty, it is displayed as "".

And I have excel data with a translation data that maps elements. Here, empty cells are displayed as null.

When I want to create a table with the value and the translation the blanks are left out. I found out that empty cells are treated differently in Excel and CSV. In Excel it is null and in CSV it is "" so I already created a a new column "Custom" for Data with null instead of "" but that also does not fix the problem, as shown here:

As soon as I add the translation "LongText", the rows with empty cells disappear. 

Does anyone have an idea how to solve this problem?

[Anonymous]

Hi @v-yangliu-msft ,

Thanks for your help, your measure works! I had another problem though, but your solution helped me figure out why it did not work for me yet:

The problem was that I have to have a bidirectional relationship between these two tables, because I have to filter in both directions. And exactly this bidirectional relationship caused the empty values to be filtered out.

To fix the problem I added a new column for your table (2):

Value2 = IF('Table (2)'[Value] == "null", "", 'Table (2)'[Value])

Then I connected both tables via this new column with a bidirectional relationship (Table[Value] and Table(2)[Value2]). Now the table was displayed correctly. I also found out that you can set 'Show Items with No Data' for the elements of a table visual but for me it worked even without this option.

Best Regards,

JM

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

According to your description, I created some data to show:

Here are the steps you can follow：

1. Create measure.

table_mapping_table1 =
SWITCH(
    TRUE(),
    MAX('Table'[Value])="e",CALCULATE(MAX('Table (2)'[LongText]),FILTER('Table (2)','Table (2)'[Value]="e")),
    MAX('Table'[Value])="m",CALCULATE(MAX('Table (2)'[LongText]),FILTER('Table (2)','Table (2)'[Value]="m")),
    MAX('Table'[Value])=BLANK(),CALCULATE(MAX('Table (2)'[LongText]),FILTER('Table (2)','Table (2)'[Value]="null")))

2. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.