cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JMPowerBI
Helper I
Helper I

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

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

Data in PQ.png

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

Translation in PQ.png

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:

Tables.png

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

Does anyone have an idea how to solve this problem?

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @JMPowerBI ,

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

v-yangliu-msft_0-1607588417438.png

2020.12.10.png

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

v-yangliu-msft_2-1607588417443.png

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.

View solution in original post

2 REPLIES 2
JMPowerBI
Helper I
Helper I

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
Community Support
Community Support

Hi  @JMPowerBI ,

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

v-yangliu-msft_0-1607588417438.png

2020.12.10.png

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

v-yangliu-msft_2-1607588417443.png

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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors