cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nbrandborg
Frequent Visitor

Lookup one value from multiple tables

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 numberxyAvailability
123xxxyyy10%
234xxxyyy20%

and

Product numberxyzAvailability
345xxxyyyzzz15%
456xxxyyyzzz22%

 

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

Product numberAvailability
12310%
23420%
34515%
45622%

 

Any great suggestions to solve this? 🙂

Best regards,
Nichlas

1 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.

View solution in original post

7 REPLIES 7
pradeept
Resolver I
Resolver I

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.

@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.

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.

View solution in original post

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

PaulDBrown
Super User II
Super User II

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






amitchandak
Super User IV
Super User IV

@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)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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?

Helpful resources

Announcements
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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors