Duplicating index and match on Power Bi from excel

[Anonymous]

Hi 

I have an equation on excel to return a value based on two criterea. I am using an index and match equation on excel that I would like to duplicate onto Power Bi. 

In power Bi these two tables will be in different sheets. not sure if that would be an issue 

[v-kkf-msft]

Hi @Anonymous ,

If you save these two tables in the same sheet, when you use PowerBI to connect to the excel, we find that you can store them in two tables.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Winniz

Thank you for your response. Unfortunately this is not what I was looking for. I belive I was not clear sorry about that.

I currently have a database in power Bi directly linked to a querry ( columns J&K on previous screenshot), I also have another excel file that's linked to power Bi (colums A to F in previous screenshot). 

I want to perform a calculation on Power Bi to get column M on the screenshot. On excel the following formula can be used:
=INDEX(D:D, MATCH(1,(A:A=J3)*(B:B=L3),0))

I would like to translate this in Power Bi.  Thank you!

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

If you use import mode to connect database, you can merge two tables by matching the name and No. columns in Power Query Editor.

And if you use direct query mode to connect database, you can try the following calculate table:

Table 2 = 
ADDCOLUMNS(
    'Table (2)',
    "x2", CALCULATE( SUM(Sheet1[x2]), FILTER( Sheet1, Sheet1[Name] = EARLIER([name]) && Sheet1[No.] = EARLIER([No.]) ) ),
    "x", CALCULATE( SUM(Sheet1[x]), FILTER( Sheet1, Sheet1[Name] = EARLIER([name]) && Sheet1[No.] = EARLIER([No.]) ) ),
    "c", CALCULATE( SUM(Sheet1[c]), FILTER( Sheet1, Sheet1[Name] = EARLIER([name]) && Sheet1[No.] = EARLIER([No.]) ) )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Winniz

Thank you so much. This works!