Power Query IndexMatch Lookup with multiple criteria

[SimonCampbell]

Folks.
I am building a Power Query model, and need help with coding a lookup with multiple criteria, similar to INDEX MATCH. The Index Mathc function is far too slow in Excel because I have large amounts of data. I suspect doing it in PowerQuery will be considerable faster.
I have two tables called NPDFmTops and OHP_PLotData
I want to return a value in the OHP_PlotData table, that comes from the NPDFmTops, based on 4 different criteria The INDEX/MATCH combo I wish to replicate in PowerQuery is

Please Login or Register  to view this content.

In otherwords
return IsuName
When Wellbore=wlbName AND
IsuLevel = "FORMATION" AND
MD RKB > IsuTopDepth AND
MD RKB <= IsuBottomDepth

I have added a small example file with 2 TABLES "OHP_PlotData" and "NPDLithostrat" both added to the Data Model as Links Only... I wish the above INDEX/MATCH combo to be built into PowerQuery adding data to the Column "FORMATION" in the "OHP_PLotData" Table

Can anyone help in writing the correct PowerQuery Code to replicate this, I'm struggling considerably

Thanks

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[SimonCampbell]

I would gladly upload a sample Workbook.. BUT I can't make it small enough, cutting out large amounts of data still only gets to 3000 KB

[AliGW]

Then you need to cut it some more. What's causing the bloat? Do you have full column references in your formulae (e.g. A:A)? Do you have a lot of conditional formatting?

We need only 10-20 rows of sample data in order to be able to offer a solution.

[SimonCampbell]

I have added a Small test version of what I'm trying to do.

[rorya]

You could load your main lookup table as a query called Formation_NPDLIthostrat, filtering it for only records of FORMATION level. Then amend your OHP_PlotData query to something like:

Please Login or Register  to view this content.

[SimonCampbell]

Rory.. if I actually include all the pertinent data in a "new" query that looks at the tables(queries) will I then only need the "add custom column" script ? I am VERY new to PowerQuery but can see the big benefits. My original problem was caused by very slow performance when I used a FILTER or INDEX/MATCH combo in excel, do you think doing it in PowerQuery will help (or for that matter PowerPivot). In my data set the TABLE NPDLithostrat I have 37000 rows. There's o point in doing this if performance doesn't improve. But I can use the Worrkbook without continually refreshing the connections, only refreshing on OPEN... what do you think?

[rorya]

if I actually include all the pertinent data in a "new" query that looks at the tables(queries) will I then only need the "add custom column" script ?

It depends what you mean by that - it's not clear to me at all. You need the lookup table as a query somewhere.

My original problem was caused by very slow performance when I used a FILTER or INDEX/MATCH combo in excel, do you think doing it in PowerQuery will help (or for that matter PowerPivot). In my data set the TABLE NPDLithostrat I have 37000 rows. There's o point in doing this if performance doesn't improve. But I can use the Worrkbook without continually refreshing the connections, only refreshing on OPEN... what do you think?

It won't take long to try it and see, so...