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

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