Lookup Table 2 and Table 3 and fill the missing values in columnes of Table 1

[DanielPasalic]

Hi all

Hopefully, someone can help me to solve the problem as I tried so many times but did not succeed. 
Basically, what I want is to fill the missing values in the following columns of

Table 1:

art.nr

art.id

art.name

by looking up the values from Table 2 and three. 

Table 1 and Table 2 have the same values in each raw:

segment id

market coverage % need

market coverage % total

Table 1 and Table 3 have the same values in each raw:

country

region

sub-region

Table 2 and Table 3 have the same values in each raw:

art.nr

art.id

art.nr and art.id are unique for each raw
segment id, country, region, sub-region, market coverage % need and market coverage % total are not unique for each raw 
but in combination (all together) should give a unique value 

Is there a way I can do some kind of lookup and fill the missing values in Table 1 from table 2 and Table 3 based on the data with same values from columns in combination,  so I have the complete data?

Help would be greatly appreciated.

Table 1

Table 2

Table 3

[sturlaws · Accepted solution]

Hi @DanielPasalic 

[market coverage % need] and [market coverage % total] is typically not columns one would use for joining. 

Looking at your data it seems like there is no unique combination of [market coverage % need], [market coverage % total], [segment id], [country], [region] and [sub region]. But not quite sure, hard to read from from your screen shots(usually easier for anyone to help you if you share a sample report or sample datasett instead of screen shots).

Anyway, if the combinations of the mentioned columns are unique, you can do the following:

1. create new columns in table 3:

Segment ID =
LOOKUPVALUE (
    'Table 2'[segment ID],
    'Table 2'[art.nr], 'Table 3'[art.nr],
    'Table 2'[art.id], 'Table 3'[art.id]
)

Segment ID =
LOOKUPVALUE (
    'Table 2'[market coverage % need],
    'Table 2'[art.nr], 'Table 3'[art.nr],
    'Table 2'[art.id], 'Table 3'[art.id]
)

Segment ID =
LOOKUPVALUE (
    'Table 2'[market coverage % total],
    'Table 2'[art.nr], 'Table 3'[art.nr],
    'Table 2'[art.id], 'Table 3'[art.id]
)

2. In table 1 create the column [art.nr] like this

art.id =
LOOKUPVALUE (
    'Table 3'[art.id],
    'Table 3'[segment id], 'Table 1'[segment id],
    'Table 3'[country], 'Table 1'[country],
    'Table 3'[region], 'Table 1'[region],
    'Table 3'[sub-region], 'Table 1'[sub-region],
    'Table 3'[market coverage % total], 'Table 1'[market coverage % total],
    'Table 3'[market coverage % need], 'Table 1'[market coverage % need],

)

If the combinations of the columns are not unique, you will get an error message

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Greg_Deckler]

@DanielPasalic - Difficult to follow. Can you post sample source data as text and the results of that sample data that you expect?