Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

BEST PRACTISE: Lookup in interval table

Hi. I have tried to put my challenge simple.

 

Input:

FACT

FACT.jpg


 
Lookup_table

Lookup_table.jpg

 


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)

FACT_expected result.jpg

 

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 7
Jimmy801
Community Champion
Community Champion

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
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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?

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors