Range Lookup in Power BI in the Power Query Editor

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

[@mdhidayat5706]

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

[@PragmaticWorks]

Glad this helped!

[@CAKimberlyLewis]

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

[@PragmaticWorks]

Thank you for watching!

[@MichaelsGaming2032]

This worked perfectly for my case, thank you !

[@PragmaticWorks]

Happy to help!

[@PradeepKumar-mh5ht]

This trick is really awesome, Thanks for sharing it.

[@tubeitz]

Brilliant! Yes, Data modelling best practices videos please

[@jamesgrant9560]

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

[@PragmaticWorks]

Thanks James, glad this video was helpful!

[@yousrymagdy1704]

sending love from egypt , thank you man

[@Abder-v4q]

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

[@beckyp860]

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

[@PragmaticWorks]

My pleasure!

[@gfbordin]

Great Solution, Thanks!

[@marteocosme5458]

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

[@PragmaticWorks]

Glad to be of help!

[@tinaflemons263]

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

[@PragmaticWorks]

Thank You Tina! -Mitchell Pearson

[@utubeAgape]

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

[@walterstevens8676]

Really keen on a data modelling course!

[@rachelwaterson3549]

Brilliant! Thank you so much

[@PragmaticWorks]

Glad you enjoyed!

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

[@olgaitskova8542]

thanks so much for this video!

[@PragmaticWorks]

You're welcome! Glad you enjoyed!

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

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

[@tramle3382]

cool tutorial thanks bro

[@ilkerozen3523]

It works... Thank you!

[@PragmaticWorks]

You're welcome!

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

[@10ozGold]

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

[@aunad___]

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

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

[@Bhavik_Khatri]

Very nice example m8.

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

[@giggt6701]

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

[@williamarthur4801]

Is there a link to blog?

[@yilu4886]

Wonderful

[@PragmaticWorks]

Thank you!

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

[@vicks2412]

Yes for saries on data modeling

[@ankitsingh-lp2zq]

That's what we want 😎😎

[@SairamBalasubramaniam]

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

[@Ilya_4276]

thanks

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

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

[@zulajenkins658]

good example ;)

[@PragmaticWorks]

Thanks!

[@sonetmollick4630]

I subscribed long time ago. :D

[@PragmaticWorks]

haha, awesome and thanks :) -Mitchell Pearson

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

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

[@ubonguttah]

Keen

[@PragmaticWorks]

Thanks!