Forum Discussion

Pretengineer's avatar
Pretengineer
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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's avatar
Pretengineer
Frequent Visitor
4 years ago

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 



v-easonf-msft's avatar
v-easonf-msft
Community Support
4 years ago

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's avatar
    Pretengineer
    Frequent Visitor
    4 years ago

    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