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!

[@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

[@beckyp860]

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

[@PragmaticWorks]

My pleasure!

[@tubeitz]

Brilliant! Yes, Data modelling best practices videos please

[@Abder-v4q]

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

[@utubeAgape]

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

[@tinaflemons263]

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

[@PragmaticWorks]

Thank You Tina! -Mitchell Pearson

[@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.

[@MamagCo]

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.

[@rachelwaterson3549]

Brilliant! Thank you so much

[@PragmaticWorks]

Glad you enjoyed!

[@olgaitskova8542]

thanks so much for this video!

[@PragmaticWorks]

You're welcome! Glad you enjoyed!

[@tramle3382]

cool tutorial thanks bro

[@aunad___]

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

[@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

[@ankitsingh-lp2zq]

That's what we want 😎😎

[@ilkerozen3523]

It works... Thank you!

[@PragmaticWorks]

You're welcome!

[@williamarthur4801]

Is there a link to blog?

[@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... ?

[@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

[@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)

[@Bhavik_Khatri]

Very nice example m8.

[@10ozGold]

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

[@mdhidayat5706]

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

[@PragmaticWorks]

Glad this helped!

[@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.

[@gopichand5717]

👏 interested

[@PragmaticWorks]

Thanks Gopi.

[@sonetmollick4630]

I subscribed long time ago. :D

[@PragmaticWorks]

haha, awesome and thanks :) -Mitchell Pearson

[@ubonguttah]

Keen

[@PragmaticWorks]

Thanks!

[@walterstevens8676]

Really keen on a data modelling course!

[@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?

[@jamesgrant9560]

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

[@PragmaticWorks]

Thanks James, glad this video was helpful!

[@MichaelsGaming2032]

This worked perfectly for my case, thank you !

[@PragmaticWorks]

Happy to help!

[@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

[@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 !

[@SairamBalasubramaniam]

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

[@PradeepKumar-mh5ht]

This trick is really awesome, Thanks for sharing it.

[@gfbordin]

Great Solution, Thanks!

[@giggt6701]

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

[@yilu4886]

Wonderful

[@PragmaticWorks]

Thank you!

[@vicks2412]

Yes for saries on data modeling

[@Ilya_4276]

thanks

[@zulajenkins658]

good example ;)

[@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"}})