Power BI vlookup?

[Ericwhv]

Thank you everyone!

i have two table

Table 1 which is showing the date

PO	OMB Ref	Date
922583377	BQ740254	19/03/2021
922583814	BQ741510	17/03/2021
922583379	BQ822800	10/03/2021
922582943	BQ822800	10/03/2021
922583215	BQ823050	10/03/2021
922582880	BQ823050	10/03/2021
922582943	BQ823050	10/03/2021
922582943	BQ823050	10/03/2021
922582895	BQ823050	10/03/2021
922582895	BQ823050	10/03/2021

Table 2 which do you have the date

Booking Number (BK)	PO Num
BQ740254	922583377
BQ741510	922583814
BQ822800	922582943
BQ822800	922583379
BQ823050	922582880
BQ823050	922582895
BQ823050	922582943
BQ823050	922583215

in Excel, we could do a vlookup to find out the first result and put the date into Table 2, but power BI, how can we do that?

the result i want to see is. .... 

Table two

Booking Number (BK)	PO Num	data by Vlookup
BQ740254	922583377	12/04/2021
BQ741510	922583814	07/04/2021
BQ822800	922582943	14/04/2021
BQ822800	922583379	14/04/2021
BQ823050	922582880	07/04/2021
BQ823050	922582895	07/04/2021
BQ823050	922582943	07/04/2021
BQ823050	922583215	07/04/2021

i try to use "Related" function, but Related function is just for one to one relationship, instead of the "many to many"

thank you!

[AlB · Accepted solution]

Hi @Ericwhv 

Best in PQ but if you want it in DAX:

NewCol =
LOOKUPVALUE ( Table1[Date], Table1[PO], Table2[PO Num] )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[v-shex-msft · Accepted solution]

Hi @Ericwhv,

In fact, you can use lookupvalue function to direct lookup other table field values if they existed related fields(e.g. table1 'id', table2 'id', you can use table1 'id' as key to search table2 other field values) even if you not link these tables with relationships.

Introducing LOOKUPVALUE - SQLBI

Regards,

Xiaoxin Sheng

[AlB · Accepted solution]

Hi @Ericwhv 

Best in PQ but if you want it in DAX:

NewCol =
LOOKUPVALUE ( Table1[Date], Table1[PO], Table2[PO Num] )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[davehus]

Hi you could achieve this in the query editor using the merge function. See link below.

https://www.youtube.com/watch?v=zHEXbCe4hig