Lookup one value from multiple tables

[nbrandborg]

Hi all,

I'm having a master table containing product numbers in scope from which I want to lookup values in from other sources/tables.
The values to one of the columns in the master table needs to lookup one value from multiple tables per product number. All product numbers are clustered in groups/tables having different columns and number of columns but all share the same column "Availability" which is comparable across. This columns is the one from which I need to lookup in. A product number only exist once across the lookup tables.

The lookup tables looks simplified like this (I'm having 10 tables)

Product number	x	y	Availability
123	xxx	yyy	10%
234	xxx	yyy	20%

and

Product number	x	y	z	Availability
345	xxx	yyy	zzz	15%
456	xxx	yyy	zzz	22%

And the master table are supposed to look like this after the lookup

Product number	Availability
123	10%
234	20%
345	15%
456	22%

Any great suggestions to solve this? 🙂

Best regards,
Nichlas

[pradeept]

hi @nbrandborg  you can try like this; I am not sure about your data model.

1. Create a lookup table, by merging all 10 tables, like this.
Product_LookupTable =
Union(
SELECTCOLUMNS(Table1, "Product number", Table1[Product number],"Availability", Table1[Availability]),
SELECTCOLUMNS(Table2, "Product number", Table2[Product number],"Availability", Table2[Availability])
)

2. Create a new column in Product Master table like thhis.
Availability =
LOOKUPVALUE(Product_LookupTable[Availability],Product_LookupTable[Product number],Product_Master[Product number])


Did I answer your question? Mark my post as a solution! It helps others.

[nbrandborg]

@pradeept it was not a bad idea, and definately not something I had considered. But tried implementing it and it worked fine! 

I considered expanding it and tried to take in another column to make the table more useful and central for lookups. Unfortunately I did not succeed in importing another column. The columns comes from the same supporting tables and has the same logic as 'availability'.
Can the function be altered to take more columns in still based on the product numbers.

[pradeept · Accepted solution]

hi @nbrandborg  I think it may work. I used the below code snippet.

1. Create loook up table like this.
Product_LookupTable =

Union(

SELECTCOLUMNS(Table1, "Product number", Table1[Product number],"Availability", Table1[Availability],"New_Column",Table1[x]),

SELECTCOLUMNS(Table2, "Product number", Table2[Product number],"Availability", Table2[Availability],"New_Column",Table2[y])

)

2. Create columns in the Product Master table like this.

Availability =
LOOKUPVALUE(Product_LookupTable[Availability],Product_LookupTable[Product number],Product_Master[Product number])

New_Column_Master =
LOOKUPVALUE(Product_LookupTable[New_Column],Product_LookupTable[Product number],Product_Master[Product number])

Did I answer your question? Mark my post as a solution! It helps others.

[nbrandborg]

This was super, super helpful! Worked like a charm!
Thank you @pradeept !

[PaulDBrown]

@nbrandborg I'm not sure I'm following your predicament. Can you post a depiction of your model?

[amitchandak]

@nbrandborg , as lookup can work across two tables only .

You might have to try something like this with 10 tables

new column =
var _1= maxx(filter(Table1, Table1[Product number] = master[Product numbe]),table1[Availability])
var _2= maxx(filter(Table2, Table1[Product number] = master[Product numbe]),table2[Availability])
//add other
Switch(true() ,
not(isblank(_1)) ,_1 ,
not(isblank(_2)) ,_2 ,

//add for other 8
0)

[nbrandborg]

@amitchandak 
Thank you for providing a solution.
I just tried to set it up partly with some of the tables, but I get an syntax error around the 'switch' expression.

Availability =

var _1= maxx(filter('EC010326', 'EC010326'[Material] = Products[Material]),'EC010326'[Availability])

var _2= maxx(filter('EC010737', 'EC010737'[Material] = Products[Material]),'EC010737'[Availability])

var _3= maxx(filter('EC011491', 'EC011491'[Material] = Products[Material]),'EC011491'[Availability])

var _4= maxx(filter('EC010980', 'EC010980'[Material] = Products[Material]),'EC010980'[Availability])

Switch(true() ,

not(isblank(_1)) ,_1 ,

not(isblank(_2)) ,_2 ,

not(isblank(_3)) ,_3 ,

not(isblank(_4)) ,_4 ,

0)

Do you spot any errors?