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
Pretengineer
Frequent Visitor

INDEX MATCH in Power BI

Hello,
I have tried a ton of suggestions in this forum but they are falling a little short for what I'm needing.
Below are samples of my data, first on the left is my pn to model reference and the second is a list of models with crazy names but they contain the model numbers from the first data table. The last table is the result I am looking for. I have this in excel but I'd like to eliminate doing this in excel first and just having it here in Power Bi.  I tried to upload the Power Bi File or the excel file but I got an error that you can't do that. 
Any help will be much appreciated.

2022-02-25_13-18-02.png

PLM_DATA
PLM_PNPLM_Model
PN144833MD456435763
PN1448343001MD32434
PN144835DM100216344
PN144830DS1616161

 

AP_DATA
aP_Model
DS1616161-F
DS1616161
MD456435763
101-MD456435763_1651
165165-3001MD32434
3001MD32434-G
3001MD32434_F
1_1_8 - DM100216344-A-AP_PART-1

DM100216344

 

AP_DATA
aP_ModelMatch _PN
1_1_8 - DM100216344-A-AP_PART-1PN144835
DS1616161PN144830
3001MD32434-GPN144834
101-MD456435763_1651PN144833
165165-3001MD32434PN144834
DS1616161-FPN144830
3001MD32434_FPN144834
MD456435763PN144833
DM100216344PN144835

 

 

 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @Pretengineer 

You can create a calculated column as below:

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

14.png

Please check my sample file for more details.

 

Best Regards,
Community Support Team _ Eason

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula in the AP_Data works

Column = lookupvalue(PLM_Data[PLM_PN],PLM_Data[PLM_Model],FIRSTNONBLANK(FILTER(VALUES(PLM_Data[PLM_Model]),SEARCH(PLM_Data[PLM_Model],AP_Data[aP_Model],1,0)),1))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-easonf-msft
Community Support
Community Support

Hi, @Pretengineer 

You can create a calculated column as below:

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

14.png

Please check my sample file for more details.

 

Best Regards,
Community Support Team _ Eason

@v-easonf-msft 

 

This worked perfect , thank you for that.  However, when I put it in my real data it had the "working" popup for about 20 minutes before I just killed the app.   With 298638 lines in the PLM_DATA and 7824 lines in the AP_DATA I think it is just too much to process.  If I calculated this right if it took a second per each CONTAINSSTRING it would take 74 years to complete.  I may need to try filtering first and get the PLM_DATA  or AP_DATA numbers smaller. 

The AP_DATA does have a another column called aP_PN where some already have valves.  Can an additonal filter be added to not scan these?
Something like 

FILTER( AP_DATA, isblank (AP_DATA[AP_PN]))

 

AP_DATA
aP_ModelAP _PN
1_1_8 - DM100216344-A-AP_PART-1 
DS1616161 
3001MD32434-G 
101-MD456435763_1651PN144833
165165-3001MD32434 
DS1616161-FPN144830
3001MD32434_FPN144834
MD456435763 
DM100216344 



Hi, @Pretengineer 

If your dataset is too large, I don't think the original solution will work for you.

Take a try this new solution.

In 'Transform Data' , try feature 'Merge Queries'  in Table 'AP_Data'  to add a new table column
(or 'Merge Queries as new' to add a new Table query )

1.png

2.png

22.png

Result:

23.png

Please check my sample file for more detail.

 

Best Regards,
Community Support Team _ Eason

 

@v-easonf-msft,

This was one other option I was looking at as well, I am a little concerned about the data I have.  Some have partial numbers that don't match to the correct model number in the other table, so the fuzzy matching may cause errors.  I will try it though.  After adding the if statement and waiting longer and the first solution worked very well.  It actually pulled over 1000 new numbers that my excel index match formula missed.

Thank you very much for you help.

Pretengineer

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.