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 III
Super User III

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 III
Super User III

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors