Range Lookup in Power BI in the Power Query Editor

In this video we take a look at how to perform a range lookup in Power BI. Learn how to lookup a value in a table where the date falls between a start date and end date! This videos focuses on how to perform this activity in the Power Query Editor.
If you enjoy this video and are interested in formal training on DAX, Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Mitchell20" to receive a discount at check out when purchasing On-Demand Learning classes from pragmaticworks...
Dimensional Modeling Book:
www.amazon.com...
#PragmaticWorks #MitchellPearson #MitchellSQL
~-~~-~~~-~~-~
Please watch: "(275) Free Pragmatic Works Power Apps Component Library "
• Free Power Apps Compon...
~-~~-~~~-~~-~

[@triovela]

Incredible! I've searched high and low for this solution. I found the blog you mentioned but you made the steps super simple to follow and understand. Thank you!

[@CAKimberlyLewis]

THANK YOU! I've been looking for this for two days! My heart is happy! 🌞

[@PragmaticWorks]

Thank you for watching!

[@mdhidayat5706]

Wow...mindblown...
The solution looks simple yet very elegant and beautifully written in PowerQuery.

[@PragmaticWorks]

Glad this helped!

[@MichaelsGaming2032]

This worked perfectly for my case, thank you !

[@PragmaticWorks]

Happy to help!

[@jamesgrant9560]

Also keen. And great video! Perfect timing for a challenge on my desk!

[@PragmaticWorks]

Thanks James, glad this video was helpful!

[@marteocosme5458]

this is exactly needed now in my project :D Thanks!

[@PragmaticWorks]

Glad to be of help!

[@yousrymagdy1704]

sending love from egypt , thank you man

[@walterstevens8676]

Really keen on a data modelling course!

[@beckyp860]

This was awesome! Thanks so much for taking the time share.

[@PragmaticWorks]

My pleasure!

[@Abder-v4q]

Many thanks i was looking to this since a lot of weeks

[@PradeepKumar-mh5ht]

This trick is really awesome, Thanks for sharing it.

[@tramle3382]

cool tutorial thanks bro

[@tubeitz]

Brilliant! Yes, Data modelling best practices videos please

[@tinaflemons263]

Mitchell great video, I would be interested in a series on data modeling

[@PragmaticWorks]

Thank You Tina! -Mitchell Pearson

[@rachelwaterson3549]

Brilliant! Thank you so much

[@PragmaticWorks]

Glad you enjoyed!

[@user-tt9kr4yr8i]

Thanks! I don't know it is still good what I did; but I just did you procedured twice, because I have a matching table inbetween :) --> but it seems to work for now.

[@utubeAgape]

This is excellent! Yes, please do a series on data modeling! Thank you - I’ve subscribed!

[@olgaitskova8542]

thanks so much for this video!

[@PragmaticWorks]

You're welcome! Glad you enjoyed!

[@gfbordin]

Great Solution, Thanks!

[@ankitsingh-lp2zq]

That's what we want 😎😎

[@johnabram4159]

Hi Mitchell, @ 2:19 you said you will put a link to your favorite dimensional modelling book. No link found! Could you please?

[@PragmaticWorks]

Hey John, sorry about that! I added it to the description. My apologies.

[@ilkerozen3523]

It works... Thank you!

[@PragmaticWorks]

You're welcome!

[@jessicalouis-marie7453]

great thanks exactly what I needed, quick question if by doing this my results still returns 3 rows (start date in my date range changed slightly) so I would like to return the most recent start date, how would I do that , was think about max startdate but can not make it work

[@srikanth01guntur]

Hi, This is excellent and very easy to understand and replicate.
Thanks for sharing here.....Only one clarification....I applied the same, by the value/rate what I tried to map, but is is creating with rounding up (Ex- If the price is to be mapped is 226.71, it is getting updated as 227), I have ensured the format in same form in all the places but luck.
Is there some thing else, I need to update as settings, Could you please suggest

[@aunad___]

how do we do this in DAX please? did you end up sharing the video? i couldn't find it

[@Bhavik_Khatri]

Very nice example m8.

[@vicks2412]

Yes for saries on data modeling

[@pieterlinden8292]

Can I use a trick like this for doing product pricing? (my "recipe" stays the same, but ingredient prices change all the time) So I basically need to get "current" prices as of the date in the current evaluation context... ?

[@yilu4886]

Wonderful

[@PragmaticWorks]

Thank you!

[@guillermolapelosa2062]

Hi, excellent video. In my case, it works in the power query editor but takes way too long to refresh in the reports tab. Any advice to solve this problem? maybe creating a column in the data tab with Dax would help to make it faster.

[@arnaultc.5790]

Same problem for me. It does not seem to be a proper solution for large datasets.

[@ivantodorcevic984]

Same problem here. Takes infinity to calc 50MB model. My best guess is that value of VAR currendate (=[auditdate] should be somehow cleared, after each record). But, I do not know how to do it. Any idea?

[@zhajar]

I think using table.buffer may help eith speed and efficiency

[@williamarthur4801]

Is there a link to blog?

[@unitedeurope8614]

I have this solution, the problem is: if there is a nul oder error value to be looked up, the query will fail in the expand step (the query will fail to load completely afer the offending record)

[@10ozGold]

Excellent video Mitchell. What's the link to the Direct Lookup using Crossjoin that was mentioned at the 5:10 mark?

[@SairamBalasubramaniam]

Any reason why this method fails if we select as Merge as a new query. It works fine with Merge Queries.

[@bravucod]

Great video, please provide the links mentioned in your tutorial. Thanks

[@PragmaticWorks]

Hey Daniel, my apologies! I added a link to the book on Amazon.

[@bravucod]

@@PragmaticWorks no apologies needed. Thanks for providing such a great content !

[@giggt6701]

May I ask where is the link of that blog you were looking at?

[@Ilya_4276]

thanks

[@siemdevlieger]

Did anyone try this with a large dataset? I have 2 tables that contain a few 100K records per table. But it keeps loading forever. Not sure if I did something wrong, or if I should continue looking for another solution. Thanks!

[@OscarPerez-hi7hq]

@PragmaticWorks second this request. It works, but not on large data sets. Any solutions?

[@gopichand5717]

👏 interested

[@PragmaticWorks]

Thanks Gopi.

[@sarahc172]

This is great! Can you do something similar but with a range of numbers rather than dates to look in? For example, return the value associated with a number between 0-99, return a different value if it falls between 100-199, return a different value if it falls between 200-299, etc.

[@zulajenkins658]

good example ;)

[@PragmaticWorks]

Thanks!

[@sonetmollick4630]

I subscribed long time ago. :D

[@PragmaticWorks]

haha, awesome and thanks :) -Mitchell Pearson

[@ubonguttah]

Keen

[@PragmaticWorks]

Thanks!

[@dizzyharris2658]

Thank you. I was able to use this to calculate an aggregation I've been trying to do for a while. Given an employee's start and end date, how many employee's were employed on any given date. I just took the Add column expression from this video and applied it to a Date Table with out joining it to my Employee Hire Dim Table.
#"Added Custom" = Table.AddColumn(#"Changed Type", "Employee Aggregate", each let currentdate = [Date] in Table.SelectRows(EmployeeHireDim, each [StartDate] = currentdate)),
#"Aggregated Employee Aggregate" = Table.AggregateTableColumn(#"Added Custom", "Employee Aggregate", {{"EmployeeID", List.Sum, "Sum of EmployeeID"}, {"EmployeeID", List.NonNullCount, "Count (Not Blank) of EmployeeID"}})