Append a column from an unrelated table based on non unique values

[Imperial56]

Hello,

I am trying to append a column to a table with values from another unrelated table. I have tried a few of suggestions found in other threads but they don't quite deliver the correct value. The main issue as there is no way I can see to create a relationship due to duplicate values.

As an example I would like to append the "Size" column from Table 2 into table 1 based on the UserID and Variety from table 1 matching the UserID and Variety from table 2.

Table 1 Fields have duplicate values.

Table 1:

User ID, Variety

1, Gold

1,Gold

1,Gold

1,Silver

2, Gold

2,Gold

2,Silver

2,Silver

3, Gold

1, Silver

2, Silver

2, Silver

Table 2:

 UserID, Variety, Size

1,Gold, 27.95

1,Silver, 22.50

2,Gold, 28.00

2,Silver, 23.00

3,Gold, 29.00

3,Silver, 23.50

The end result should look:

User ID, Variety, Size

1, Gold,27.95

1,Gold,27.95

1,Gold,27.95

1,Silver,22.50

2, Gold,28.00

2,Gold,28.00

2,Silver,23.00

2,Silver,23.00

3, Gold,29.00

1, Silver,22.50

2, Silver,23.00

2, Silver,23.00

Any ideas or assistance greatly appreciated.

Regards

Imperial56

[Zubair_Muhammad · Accepted solution]

@Imperial56

You can add a calculated column in Table 1 to LOOKUP the size from Table 2 using LOOKUPVALUE function

size =
LOOKUPVALUE (
    Table2[Size],
    Table2[UserID], Table1[User ID],
    Table2[Variety], Table1[Variety]
)

[Phil_Seamark · Accepted solution]

Hi @Imperial56

This version of a calcualted column won't break if your lookup table returns multiple results.

New Column = MINX(
                FIlTER(
                    'Table2',
                    'Table2'[User ID]=EARLIER('Table1'[User ID]) &&
                    'Table2'[Variety] = EARLIER('Table1'[Variety])
                    ),
                'Table2'[Size])

[Ashish_Mathur]

Hi,

Using the Query Editor, simply join the two tables based on User ID and variety.

[Phil_Seamark · Accepted solution]

Hi @Imperial56

This version of a calcualted column won't break if your lookup table returns multiple results.

New Column = MINX(
                FIlTER(
                    'Table2',
                    'Table2'[User ID]=EARLIER('Table1'[User ID]) &&
                    'Table2'[Variety] = EARLIER('Table1'[Variety])
                    ),
                'Table2'[Size])

[parry2k · Accepted solution]

I would recommend to create key in both the tables by concatenating userid + variety and set relationship on this new key column, then it is much easier.

[Phil_Seamark]

You can unwittingly introduce key collisions and other issues when using composite keys.  LookUpValue is pretty inefficient I agree

[Zubair_Muhammad · Accepted solution]

@Imperial56

You can add a calculated column in Table 1 to LOOKUP the size from Table 2 using LOOKUPVALUE function

size =
LOOKUPVALUE (
    Table2[Size],
    Table2[UserID], Table1[User ID],
    Table2[Variety], Table1[Variety]
)