Range Lookup in Power BI in the Power Query Editor

No video

Range Lookup in Power BI in the Power Query Editor

Рет қаралды 28,442

Күн бұрын

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...
~-~~-~~~-~~-~

Пікірлер: 66

@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 2 жыл бұрын

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

@PragmaticWorks 2 жыл бұрын

Glad this helped!

@tramle3382 2 ай бұрын

cool tutorial thanks bro

@jamesgrant9560 2 жыл бұрын

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

@PragmaticWorks 2 жыл бұрын

Thanks James, glad this video was helpful!

@marteocosme5458 2 жыл бұрын

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

@PragmaticWorks 2 жыл бұрын

Glad to be of help!

@MichaelsGaming2032 Жыл бұрын

This worked perfectly for my case, thank you !

@PragmaticWorks Жыл бұрын

Happy to help!

@beckyp860 Жыл бұрын

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

@PragmaticWorks Жыл бұрын

My pleasure!

@yousrymagdy1704 Жыл бұрын

sending love from egypt , thank you man

@walterstevens8676 Ай бұрын

Really keen on a data modelling course!

@user-hd5ub6iq1t Жыл бұрын

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

@PradeepKumar-mh5ht Жыл бұрын

This trick is really awesome, Thanks for sharing it.

@tinaflemons263 2 жыл бұрын

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

@PragmaticWorks 2 жыл бұрын

Thank You Tina! -Mitchell Pearson

@tubeitz 2 жыл бұрын

Brilliant! Yes, Data modelling best practices videos please

@rachelwaterson3549 2 жыл бұрын

Brilliant! Thank you so much

@PragmaticWorks 2 жыл бұрын

Glad you enjoyed!

@olgaitskova8542 2 жыл бұрын

thanks so much for this video!

@PragmaticWorks 2 жыл бұрын

You're welcome! Glad you enjoyed!

@gfbordin 7 ай бұрын

Great Solution, Thanks!

@utubeAgape 2 жыл бұрын

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

@ankitsingh-lp2zq 10 ай бұрын

That's what we want 😎😎

@user-tt9kr4yr8i 6 ай бұрын

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.

@ilkerozen3523 2 жыл бұрын

It works... Thank you!

@PragmaticWorks 2 жыл бұрын

You're welcome!

@johnabram4159 2 жыл бұрын

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

@PragmaticWorks 2 жыл бұрын

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

@Bhavik_Khatri Жыл бұрын

Very nice example m8.

@yilu4886 Жыл бұрын

Wonderful

@PragmaticWorks Жыл бұрын

Thank you!

@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

@vicks2412 2 жыл бұрын

Yes for saries on data modeling

@aunad___ 4 ай бұрын

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

@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 10 ай бұрын

Is there a link to blog?

@10ozGold 2 жыл бұрын

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

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

@bravucod 2 жыл бұрын

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

@PragmaticWorks 2 жыл бұрын

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

@bravucod 2 жыл бұрын

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

@Ilya_4276 2 жыл бұрын

thanks

@gopichand5717 2 жыл бұрын

👏 interested

@PragmaticWorks 2 жыл бұрын

Thanks Gopi.

@sarahc172 2 жыл бұрын

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.

@SairamBalasubramaniam 2 жыл бұрын

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

@giggt6701 Жыл бұрын

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

@unitedeurope8614 10 ай бұрын

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)

@zulajenkins658 2 жыл бұрын

good example ;)

@PragmaticWorks 2 жыл бұрын

Thanks!

@siemdevlieger 5 ай бұрын

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 4 ай бұрын

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

@sonetmollick4630 2 жыл бұрын

I subscribed long time ago. :D

@PragmaticWorks 2 жыл бұрын

haha, awesome and thanks :) -Mitchell Pearson

@ubonguttah 2 жыл бұрын

Keen

@PragmaticWorks 2 жыл бұрын

Thanks!

@dizzyharris2658 11 ай бұрын

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