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 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_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

 

 

 

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

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      This solution works great for my need, thank you, do you think it also works fine for large data?

      thanks again.

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    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