how to 'vlookup' powerbi table with multiple values

[Anonymous]

hi all,

i am new to PowerBI and am facing an issue, any comments or help is much appreciated.

i have 2 tables at the moment, table 1 and table 2 which looks like this:

Table1

type 1 , apple

type 1, apple

type 2, cheese

type 3, bake

type 3, bake

Table2

type 1 , chef1

type 1, chef2

type 2, cook

type 3, helper1

type 3, helper2

what i would like to achieve is below:

Table1

type 1 , apple , chef1

type 1, apple , chef1

type 2, cheese , cook

type 3, bake , helper1

type 3, bake, helper1

basically a 'vlookup' from table1 to table2 taking only the first value.

i have tried LOOKUPVALUE but it returns an error of 'multiple values was supplied where a single value was expected'

is there another way for me to achieve this?

any help is appreciated, thank you

[mangaus1111]

hi @Anonymous ,

see my solution in power query

https://1drv.ms/u/s!Aj45jbu0mDVJkQfE8jZj3XGTet5A?e=470G5e

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous ,

Try a new column in table 1

minx(filter(Table2, Table1[type] = Table2[Type] ) , Table2[Value] )

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[jpbi23]

is there a way to use the same formula but so it excludes blank values?

[Anonymous]

works like a charm! thank you very much sir!