Forum Discussion
BEST PRACTISE: Lookup in interval table
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?
7 Replies
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
JimmyHello 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
