BEST PRACTISE: Lookup in interval table

[Anonymous]

Hi. I have tried to put my challenge simple.

Input:

FACT

 

 
Lookup_table

 

Now, what I would like to do is

Add column to FACT, where

- The FACT[some number] is looked up in Lookup_table.

Expected result

FACT (after)

 

 

What I have done, as you see in the formula bar, is to make a function used in the each iterator. Then, Table.Buffer(Lookup_table) and filter Lookup_table and return the filtered table. 

let Source = (lookuptable as table, lookup_fromCol as text, lookup_toCol as text, somevalue as number) as table => let Selectrows = Table.SelectRows(lookuptable, each Table.Column(_, lookup_fromCol) <= somevalue and Table.Column(_, lookup_toCol) >= somevalue ) in Selectrows in Source 

This works just fine. However, when I scale this up, I get the feeling that it's not as efficient as it should be. The equivalent use of VALUES() in DAX is much more efficient, as far as I can tell.

What am I doing wrong here? How can this function be written to gain better efficiency?

[Jimmy801]

Hello @Anonymous 

what you can do is to add a new column where you use a Table.SelectRows. Here some code that you get an idea what I mean

Table.SelectRows(Table.Buffer(Lookup_table), (sel)=> sel[from number]<_[some number] and sel[to number]>_[some number])

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Sorry for the late reply.

How is this different from my approach? I don't understand what is achieved here. Perhaps you could elaborate? 

[Jimmy801]

Hello @Anonymous 

I'm using Table.Buffer and I don't user Table.Column. Don't even know how Table.Column can work in this circumstances. Did you try my approach?

BR

Jimmy

[Anonymous]

Hi Jimmy

I have come back to this post a few times trying to understand your answer. What bugs me is, that I don't see how I can implement your approach.

Your answer: "what you can do is to add a new column where you use a Table.SelectRows. Here some code that you get an idea what I mean"

Table.SelectRows(Table.Buffer(Lookup_table), (sel)=> sel[from number]<_[some number] and sel[to number]>_[some number])

1) I don't understand what you mean by "add a new column". I see you using Table.SelectRows, not adding any columns. 

2) My use of Table.SelectRows is nested in a function, hence I cannot call _[some number]. I am thinking _[some number] is not meaningful in this context? It is a constant, not a field value of a record. 

3) I cannot call sel[from number], again because I nest this in a function - this is the whole idea behind me using Table.Column(). I want to be able to reference the column in the function, as I do not know the column name beforehand.  

Example: lookup_fromCol as text, lookup_toCol as text these two strings are inputs to my function, and they represent the corresponding column names within Lookup_table.

Please fire away if any further details are needed. 

Thank you!

Edit: This is the #"Lookup function" :  (also stated, but not named in the initial question)

let Source = (lookuptable as table, lookup_fromCol as text, lookup_toCol as text, somevalue as number) as table => let Selectrows = Table.SelectRows(lookuptable, each Table.Column(_, lookup_fromCol) <= somevalue and Table.Column(_, lookup_toCol) >= somevalue ) in Selectrows in Source 

[Jimmy801]

Hello @Anonymous 

the code I posted was meant to add as formula once you add a new column in the GUI.

However to accelerate this you could try:

- go to advanced editor

- Add a new line where you write BufferedLookUpTable= Table.Buffer(#"Lookup_table"),

- change the value #"Lookup_table" to BufferedLookUpTable into your addcolumn-step

This means that you are buffering the lookup in a variable and giving this buffered table to invoke your funktion. So it's not needed to reread it on every row. This should do it.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi Jimmy

Unfortunately, I have already done this, as I stated in the initial question. The #"Lookup table" query is

let Source = Excel.CurrentWorkbook(){[Name="Lookup_table"]}[Content],
in Table.Buffer(Source)

I have also tried putting the Table.Buffer part in the function itself, and to me it seems the effect is identical - both equally efficient?

[Jimmy801]

Hello @Anonymous 

I think you have to add the table buffer in the query, where you are merging both of your queries, not in the orignal one

BR

Jimmy