INDEX MATCH in Power BI

[Pretengineer]

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.

PLM_DATA
PLM_PN	PLM_Model
PN144833	MD456435763
PN144834	3001MD32434
PN144835	DM100216344
PN144830	DS1616161

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_Model	Match _PN
1_1_8 - DM100216344-A-AP_PART-1	PN144835
DS1616161	PN144830
3001MD32434-G	PN144834
101-MD456435763_1651	PN144833
165165-3001MD32434	PN144834
DS1616161-F	PN144830
3001MD32434_F	PN144834
MD456435763	PN144833
DM100216344	PN144835

[Ashish_Mathur]

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.

[v-easonf-msft · Accepted solution]

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] ) )
)

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[Pretengineer]

@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_Model	AP _PN
1_1_8 - DM100216344-A-AP_PART-1
DS1616161
3001MD32434-G
101-MD456435763_1651	PN144833
165165-3001MD32434
DS1616161-F	PN144830
3001MD32434_F	PN144834
MD456435763
DM100216344

[v-easonf-msft]

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 )

Result：

Please check my sample file for more detail.

Best Regards,
Community Support Team _ Eason

[Pretengineer]

@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