Forum Discussion
BEST PRACTISE: Lookup in interval table
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
Sorry for the late reply.
How is this different from my approach? I don't understand what is achieved here. Perhaps you could elaborate?
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
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