Create a Join based on Date Range in Power Query

Рет қаралды 11,773

Күн бұрын

Пікірлер: 65

@BIGorilla 7 ай бұрын

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/

@scramiro1 7 ай бұрын

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.

@GrainneDuggan_Excel 7 ай бұрын

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!

@BIGorilla 7 ай бұрын

It will be easier by than. For sure!

@williamarthur4801 7 ай бұрын

As always learnt something new, data type can go in curly brackets. never occurred to me before. Thank you.

@BIGorilla 7 ай бұрын

You're very welcome !

@kuuuyajim 2 ай бұрын

This is exactly what I'm looking for. Method 1 is definitely more complex but it's what my data model requires since I am working on a historical roster which can have more than 1k employees. Knowing Method 2 can be handy too when working with small datasets. Thank you!

@Bhavik_Khatri Ай бұрын

Method 2 was straightforward to comprehend. I appreciate your video and explanations.

@YvesAustin Ай бұрын

This can come very useful when handling payroll for example with not only dates but also start times and end times for various shifts and overtime categories. I had initially created a merge using two null columns as a join and then filtered out the null values. This solution is def more elegant.

@Ganja1974 Ай бұрын

Always used 2nd method. Thank for you 1st method!

@vinhwizard 7 ай бұрын

Thank you so much for beautiful methods

@BIGorilla 7 ай бұрын

You are so welcome!

@ThepExcel 7 ай бұрын

Already bought your new book yesterday. Thx for great knowledge as always

@ryanbartlett672 2 ай бұрын

I was already planning on buying the book based Rick's content; this video/article nudged my mouse over the buy button. Got it yesterday. Thanks.

@ryanbartlett672 2 ай бұрын

I also noticed a fellow Snagit as you go practioner

@CeliaAlvesSolveExcel 7 ай бұрын

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

@BIGorilla 7 ай бұрын

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 🙌

@carloserodriguez2465 7 ай бұрын

I bought the book and I am going thru the Chapter 4. Totally recommend it!!!!

@BIGorilla 7 ай бұрын

You’re awesome! ❤

@drewbbc7074 5 ай бұрын

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.

@lashes667 2 ай бұрын

Thanks! Great examples

@MrJadison7 7 ай бұрын

Hello Rick, thank you for the video. Can you tell which of the 2 methods works better performance wise

@BIGorilla 7 ай бұрын

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!

@marcosmel1087 17 күн бұрын

Very Interesting!

@Bhaskar_Joshi_there 7 ай бұрын

Loved it

@BIGorilla 7 ай бұрын

Thanks buddy!

@scramiro1 7 ай бұрын

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.

@BIGorilla 7 ай бұрын

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 :)

@baskis69 7 ай бұрын

Brilliant! Thanks for sharing...the book is on its way...looking forward to continue learning with the book!

@BIGorilla 7 ай бұрын

Wonderful, hope you enjoy it! 🎉

@petervanbussel7975 6 ай бұрын

Very nicely explained. Wonder how this works when you have two tables with different date ranged that needs to be merged.

@giuseppefenzi2942 6 ай бұрын

Thank you, brilliant as usual. I bought the book!

@bclark1966 2 ай бұрын

Bought the book!

@danielgoncalveslima9350 7 ай бұрын

In the first example, would making a reference to the outside table without attaching it to the main table change performance?

@williamarthur4801 7 ай бұрын

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.

@BIGorilla 7 ай бұрын

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

@williamarthur4801 7 ай бұрын

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

@BIGorilla 7 ай бұрын

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

@CSJMyDrive 4 ай бұрын

Thank you.

@IntellikronTech Ай бұрын

Great!

@ExcelInstructor 7 ай бұрын

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?

@BIGorilla 7 ай бұрын

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!

@ExcelInstructor 7 ай бұрын

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

@BIGorilla 7 ай бұрын

@@ExcelInstructor I might measure the performance later and report back. Also, I'm glad to find you back on the channel. Thank you!

@bertbrug 6 ай бұрын

For the second method, when I merge into the transactional query. What happens when campaigns have overlapping periods?

@jerrydellasala7643 7 ай бұрын

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

@BIGorilla 7 ай бұрын

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 !

@rrrraaaacccc80 4 ай бұрын

Great 💯👍

@RobertoStaltari 13 күн бұрын

Microsoft deserves criticism on two fronts: first, for providing a user interface that spoils users, and second, for not offering a proper editor (at least a search function please!) for those who want to unlock the full power of M.

@nishantkumar9570 7 ай бұрын

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.

@BIGorilla 7 ай бұрын

I will do some performance testing and report back on the performance in the written blog article!

@kelvinalmarez5495 2 ай бұрын

Appiled the method 1 but it takes a lot of time to load in report view. Is that normal or may data is just too big? Schedule table is 11k rows and log table is 50k rows.

@ferdirosa7547 7 ай бұрын

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.

@BIGorilla 7 ай бұрын

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’

@ferdirosa7547 7 ай бұрын

@@BIGorilla Dankjewel Rick