Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cyrilbrd
Helper IV
Helper IV

INDEX MATCH equivalence?

I have several queries that are located on a server.
One query has a complete set of data such as:

TABLE 1

Code1Code2
12345678whatevercode1
12345679whatevercode2 
12345680whatevercode3

I would like the return the corresponding Code2 based on the existing Code1 in another table from another query.
In Excel I would use something along an INDEX MATCH to get the code.
Note, Code1 MIGHT not be unique, duplicates may exists, but Code2 would always be the same for said Code 1
Excpected result:
New query table

TABLE 2

QtyCode1ExtractedCode2
2312345678whatevercode1
34512345679whatevercode2 
1212345680whatevercode3
1 ACCEPTED SOLUTION
Idrissshatila
Super User
Super User

Hello @Cyrilbrd ,

Try this
Extracted Code 2 = LOOKUPVALUE('Table1'[Code2],'Table1'[Code1],'Table2'[Code1])

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

 

Follow me on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Idrissshatila
Super User
Super User

Hello @Cyrilbrd ,

Try this
Extracted Code 2 = LOOKUPVALUE('Table1'[Code2],'Table1'[Code1],'Table2'[Code1])

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

 

Follow me on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




@Idrissshatila 

Thanks

I tried a variation of the following:

CALCULATE (
    MAX ( PLM_DATA[PLM_PN] ),
    FILTER ( PLM_DATA, CONTAINSSTRING ( AP_DATA[aP_Model], PLM_DATA[PLM_Model] ) )

But the query would not load.
The proposed solution is fast to load and works.
Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.