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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors