CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Ray
Ray

Reputation: 93

Need to get remapped values of table using Power BI

I have two tables.

  1. In first table I have some keys in Column "Key"
  2. In Second table, I have some mapping data, where for the key i have mapped some values.

Now, if I import two tables, and I select Key in first table and value in Second table, I will get mapped values of the corresponding key. But if the key is not present in the mapping data table, the key is not showing in the Output.

I need to get the key as value in Output.

Example:

Table 1
------------
Key
A
B
C
D

Table 2
--------------
Key Value
A     A1
B     B1
C     B2

Output
-----------
Key Value
A     A1
B     B1
C     B2

Expected Output
--------------
Key   Value
A      A1
B      B1
C      B2
D      D

Upvotes: 0

Views: 1216

Answers (1)

Agustin Palacios
Agustin Palacios

Reputation: 1204

There are many ways to solve this problem. I will show you how i did it using DAX.

The first thing you need to do is create a relationship between both tables: enter image description here

Then go to Table 1 and create the following column using DAX:

Value = 
VAR __value = CALCULATE( DISTINCT( 'Table 2'[Value] ), RELATEDTABLE( 'Table 2' ) )
Return 
    IF(
        ISBLANK( __value ),
        'Table 1'[Key],
        __value
    )

This will create the expected result:

enter image description here

This will only work if you have only one value for every key.

Upvotes: 0

Related Questions