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

Power BI vlookup?

Thank you everyone!

i have two table

Table 1 which is showing the date

POOMB RefDate
922583377BQ74025419/03/2021
922583814BQ74151017/03/2021
922583379BQ82280010/03/2021
922582943BQ82280010/03/2021
922583215BQ82305010/03/2021
922582880BQ82305010/03/2021
922582943BQ82305010/03/2021
922582943BQ82305010/03/2021
922582895BQ82305010/03/2021
922582895BQ82305010/03/2021

Table 2 which do you have the date

Booking Number (BK)PO Num
BQ740254922583377
BQ741510922583814
BQ822800922582943
BQ822800922583379
BQ823050922582880
BQ823050922582895
BQ823050922582943
BQ823050922583215

 

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 Numdata by Vlookup
BQ74025492258337712/04/2021
BQ74151092258381407/04/2021
BQ82280092258294314/04/2021
BQ82280092258337914/04/2021
BQ82305092258288007/04/2021
BQ82305092258289507/04/2021
BQ82305092258294307/04/2021
BQ82305092258321507/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!

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

Hi @Ericwhv 

Best in PQ but if you want it in DAX:

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

 

SU18_powerbi_badge

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.

 

View solution in original post

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

AlB
Super User
Super User

Hi @Ericwhv 

Best in PQ but if you want it in DAX:

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

 

SU18_powerbi_badge

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.

 

View solution in original post

davehus
Resolver II
Resolver II

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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!