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.

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 🙌

You're very welcome !

It will be easier by than. For sure!

You’re awesome! ❤

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

You are so welcome!

Wonderful, hope you enjoy it! 🎉

Thanks buddy!

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 !

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!

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

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

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!

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 🙏🌞

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

Hey Sameh - absolutely , when you buy the printed book, you can get the pdf with it for free. Hope that helps :)

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.