Create a Join based on Date Range in Power Query
Вставка
- Опубліковано 5 сер 2024
- This video shows two methods to create a join between tables based on a range of dates.
You can buy the Definitive Guide to Power Query M here: geni.us/ODZl8
CONTENTS
00:00 Introduction
01:16 Data
01:55 Method 1: Date range join
08:21 Method 2: Explode dates
WRITTEN ARTICLE:
gorilla.bi/power-query/join-o...
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ua-cam.com/users/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#Join #powerquery #bigorilla
For method 1, don't forget to add Table.Buffer to the Campaign table. Totally forgot showing that in the video. Find the written article at: gorilla.bi/power-query/join-on-date-range/
Thanks. I would appreciate a video about this function. I know the formula has two options. People use it normally at the beginning to buffer the table, but it can be used at the end too of the query step. I can't get around what option to use in what circumstances. Do should we always buffer at the beginning or end? A video would be great, thanks, because I haven't found any.
So much to learn in this video! It is bookmarked. Thank you, Rick!
I particularly enjoyed the concepts of inner and outer contexts. Had I known this before and I could have used it a couple of times already.
One little request for your next videos: if you could please apply more zoom in, it would be great! On this one, it was nearly impossible for my old eyes to read the code. Good thing that your explanation was perfect and helped me fill in the blurs.😊
Hey Celia - thank you! Yes, that inner and outer context can be confusing. Also appreciate the feedback on the text size. I will try to improve that on some of the next videos 🙌
To me, the second example is easier to understand. Perhaps when I reach chapter 9 of the Definitive Guide to Power Query (M) , I will find them both easy. I loved first 4 chapters that I have worked through so far. Great book Rick!
It will be easier by than. For sure!
Thank you, brilliant as usual. I bought the book!
All - i) approach number 2 is being used to manage a contract mgmt tool that expands to 20k row transaction table - all with very low latency. ii) IMV, approach number 2 is easier to debug. Sample code is available if anyone is interested.
Already bought your new book yesterday. Thx for great knowledge as always
Thank you so much for beautiful methods
You are so welcome!
Brilliant! Thanks for sharing...the book is on its way...looking forward to continue learning with the book!
Wonderful, hope you enjoy it! 🎉
I bought the book and I am going thru the Chapter 4. Totally recommend it!!!!
You’re awesome! ❤
Thank you.
As always learnt something new, data type can go in curly brackets. never occurred to me before.
Thank you.
You're very welcome !
Great 💯👍
Loved it
Thanks buddy!
Love it! I am working on a similar scenario. I used the first scenario, but I think the second is easier and faster 😅. I will definitely buy your book and subscribe to your channel.
I agree with you. The first concept is great for understanding M though, or for joins that require more complex conditions. There's something there for everyone :)
Very nicely explained. Wonder how this works when you have two tables with different date ranged that needs to be merged.
Hello Rick, thank you for the video. Can you tell which of the 2 methods works better performance wise
If I had to guess, I would say it's method 2 where we perform a regular left outer join. But I should test this be sure!
In the first example, would making a reference to the outside table without attaching it to the main table change performance?
I shall certainly be looking into getting the book, BTW have you thought about a video on the
fuzzies, nested join , cluster columns , cluster group etc., or how about the most inventive use of list.alternate ?
I'll have to watch this again and work through properly, always enjoy your work.
Hey William - definitely some good topics to look into. List.Alternate I don't use so much. And the fuzzies not either. But that would make it extra relevant. Thanks for your suggestions!
Rick
@@BIGorilla the only thing I've use list alt for is to create a list to feed to
list .Accumulate ( .... Table Insert Rows (
let rc = Table.RowCount ( Source ) ,
grupe = 5, skip = 3 , rowinsert = 3 ,
slist ={ 0.. ( Number.IntegerDivide( rc, grupe) * rowinsert)+rc },
altlist = List.Alternate( slist, (grupe + rowinsert) , 1 )
in altlist
you then have to have a ckeck to gurpe / row count = number Int divide ( grupe , orwcount ) so as not to end up with extra rows inserted at bottom of table , if true
list remvoe last,
Oh, I finally got to grips with how you List Gernerate running total actually works.
😊😊
@@williamarthur4801 Thanks for sharing your great example. I was just thinking. You'll find an example using List.Alternate in my new article: gorilla.bi/power-query/switch/. Maybe it provides some new inspiration :)
For the second method, when I merge into the transactional query. What happens when campaigns have overlapping periods?
Great techniques! BTW, that yellow dot is VERY OBTRUSIVE blocking code when trying to follow along. Of course that wouldn't be such a problem if the Workbook were provided...
Jerry! Yes I agree. I’ll get rid of that yellow dot.
By the way. Did you check out the video description? It’s got the written article url. And guess what. It has the code readily available for you to use in your workbook.
You’re welcome 🙋♂️ enjoy !
Hi Rick, Great Video, a question tho, why use 2 separate indicators T and C, the C one alone should be sufficient.
So was it just to demonstrate what each is?
That's a great question. As you say, it would be plenty to use a single indicator, and leave 'each' for one of the two functions. I used this to make it very explicit of what's happening. The thing is, when we use 'each', which is equivalent to '(_) =>', you will not see the underscore used after. That is because the simplified expression can leave out the underscore for referencing fields.
I felt making this more explicit would be helpful for a reader. Glad you asked, thanks!
@@BIGorilla I know PQ quite well, thats why I asked, and honestly both solutions will work ofcourse, but also to me the second would be beating around the bushes solution, where the 1st one is str8 forward to the expected result. I dealt with extremly hard cases in my previous work, so if I can achive 1 step (1st solution) a way of combining data I will go for it since then the rest of the code will be smaller aswell, However the second solution is great one for someone less expierienced, and yes im talking here about when I learned PQ, I would go for that back then. Anyway - glad your still doing videos! you dealt with some problems I had in the past and I was able to use fully or partialy your ideas to suit what needed. so Thank you for your tutorials in PQ!
@@ExcelInstructor I might measure the performance later and report back. Also, I'm glad to find you back on the channel. Thank you!
I always wonder is Merging operation in Power Query is a expensive in terms of computation point of view. How backend engine works?
Thanks so much for such neat tricks.
I will do some performance testing and report back on the performance in the written blog article!
Hi, Rick, Is it possible to have the sample files for practice? thanks,
Theres no file, but the code is available for free on gorilla.bi/power-query/join-on-date-range/.
You can then paste it into your queries 🙏🌞
Can i get free pdf one from this book
Hey Sameh - absolutely , when you buy the printed book, you can get the pdf with it for free. Hope that helps :)
Goedenmorgen Rick, ik werk vaak met veel kolommen die ik nu horizontaal moet slepen om ze in een bepaalde volgorde te zetten. Ik meen dat ik ooit gezien had dat je ze ergens in een menu verticaal kunt slepen maar ik kan het niet terugvinden. Weet jij dat? Dankjewel.
Hey Ferdi - ja dat is soms onhandig. Je zou ook rechterknop kunnen doen op een kolom. Dan staat er iets van ‘Move’ - move to beginning’ of ‘move to end’.
Je kunt als alternatief ook de kolommen selecteren in de volgorde die je wilt. Wanneer je alles hebt geselecteerd, kies je dan ‘Remove Other Columns’. Dan staan de kolommen ook in de gewenste volgorde.
Het menu wat jij bedoelt is denk ik degene die je ziet wanneer je in de ‘Home’ tab van de ribbon kiest voor ‘choose columns’
@@BIGorilla Dankjewel Rick
Plz zoom in
Pwbi is very nice, but as soon as you plug a table with 100 mm tables it becomes a trash for any kind of joins oi treatments!
Buffering operations like group by, pivot, sort etc are expensive. Who knows you can offload them to your datawarehouse. Would be good :)
And knowing how formula and storage engine work also can help you overcome challenges when working with large tables
@@Nazeerul_Hazard I wish Power Query had such engines. You're gonna have to look at the Vertipaq engine for DAX for that unfortunately.