Forum Discussion
BEST PRACTISE: Lookup in interval table
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
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
Hi Jimmy
Unfortunately, I have already done this, as I stated in the initial question. The #"Lookup table" query islet 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?
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