Extract Records with Consecutive Numbers - Power Query Group By GroupKind.Local. EMT 1807
Вставка
- Опубліковано 13 лип 2024
- Download Excel File: excelisfun.net/files/EMT1806-...
Learn how to use Power Query extract all records for weights that occur consecutively 3 or more times.
Topics:
1. (00:00) Introduction
2. (00:06) Review consecutive number tricks
3. (00:32) Create Names From Selection feature (Ctrl + Shift + F3) to create Defined Name for Extract Records Hurdle
4. (01:04) Import Power Query Input From Excel Worksheet and use in query, Keyboard to import data from worksheet into Power Query: Right-Click, G
5. (02:10) Import Excel Table into Power Query with keyboard
6. (02:24) Group By Feature to group by Consecutive Occurrences using 4th argument in Table.Group Power Query M Code function: GroupKind.Local.
7. (07:10) Summary, Closing, Video Links
Here is a Dynamic Array Formula video by Bhavya Gupta that accomplished the same goal as this Power Query video: • Extract Records with C...
Mike! Great video! You are the OG UA-cam Excel guru! I don't understand why your sub count hasn't hit 1 million!
Thanks, Cary B. this coming Feburary will be 15 years at UA-cam. There are a few other OGs like Mr Excel . The reason that I have not hit 1 m is because I don't specifically construct videos to target the people who want quick and easy answer to a hard problem. You know the UA-cam videos: Top 10... 1 minute to solve... BEST solution ever: most are click bait for people who want easy answers to hard problems. Since there is usually not an easy answer to hard problems, I don't make those types of videos. I make the videos with the whys and hows - you know - the videos with all the details. Because only a small slice of Excel users want the hows AND whys, my detailed videos are not for most people. My videos are for those who want the power of the whys so that they can then creatively apply the whys to unique problems to come up with solutions. Or maybe it is just that I am annoying guy at UA-cam and people don't like that lol
P.S. I once asked a Power BI DAX UA-camr why they don't talk about the "double count problem that can occur when there is no primary key and you invoke Context Transition (Row Context invoking Filter Context)"? This person said that people don't want to hear that - they don't want to be bogged down with such details". He was right. Videos sell better without such details. I said to him: but that is cheating your viewers because this deadly problem is so hard to detect and can cause the numbers to come out completely wrong. If you don't teach them this at the beginning when they are learning, it is like giving a chain saw to a person without instructions. I was very surprised to hear the UA-camr's answer because to me, it is my duty to set people up to succeed with all the details. It is more fun that way, at least for those of us that like to get correct results : )
@@excelisfun
What you said above is exactly the reason I like your videos (as well as others that do the same). I want to understand why these formulas work so that I can apply them to the problems I run into at work. What I do at work is not exactly like the examples you provide. When I understand the concepts, then I can adjust the formulas for what I need them to do.
Thanks for all you do!
@@msmith3090 You are welcome, M Smith!!!! Tell all your friends about exclisfun (at least the why ones lol)!!!!
@ExcelIsFun I already have recommended to them. 😃
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher Syed Muzammal Muhasin!!!!!!! : ) : )
Just seen here super cool solutions, all using lot of functions vstack, drop, take, offset, xmatch, or even frequency .
SCAN's acumulator can cover that functionality alone when is "scanning"✌😉
=LET(a,Weights[Weight],n,K5,FILTER(Weights,
SCAN(0,SEQUENCE(ROWS(a)),
LAMBDA(v,i,IF(IFERROR(AND(INDEX(a,i)=INDEX(a,SEQUENCE(n,,i))),0),i+n-1,IF(v>=i,v,0))))))
More awesomeness of elegant beauty from Excel Lambda : ) : ) : ) I have added it to the download workbook. Go Team!!!
Boom!Great Bit Of Power Query Fun + Awesome keyboard shortcuts Happy Days...Thank You Mike :)
Keyboard short cuts rule, Bike Brother!!!! Right-Click, G!!!!!
Great Mike! Thanks!!
You are welcome, Wayne!!!!!
thank you for the valuable tutorials. Subscribed & definately thumb up 👍
You are welcome : ) : )
Thank You my Teacher for this trick ,
for Calculate Max Consecutive Occurrences for Each Unique Number this my try
=LET(
x,Weights[Weight],
a,SCAN(0,x,LAMBDA(a,d,
IF(OR(d=N(OFFSET(d,{-1,1},))),1+a,0))),
z,MAX(a),
IFNA(HSTACK(z,UNIQUE(FILTER(x,z=a))),z))
Awesome!!!! Please post this formula solution below the video i post on Monday : ) : ) : ) We will have Team fun with " Max Consecutive Occurrences for Each Unique Number" on Monday. In addition, since the title of the video is " Max Consecutive Occurrences for Each Unique Number", when you post your formula solution there, anyone looking for this type of solution will find your great formula. Posting it here below this video has less utility.
@@excelisfun Thank You
I added your formula to download workbook, but it delivered the results: 4, 300
Wow, epic Mike! Great video
Glad you like it, Chris M!!!! : ) : )
Thanks mike!
You are welcome, Mr Coffee Guy!!!!
Thanks Mike, I really enjoy PQ videos. That hidden feature or 4th argument is like finding a gem. Please do more PQ videos.
I do love the power of Power Query too. I always seem to have so many class videos to make, that I can't do more advanced Power Query and Dynamic Spilled array videos... The good news is that in the MECS class (which will extend till next spring with (hopefully) about 2 videos per month, will have more power query videos. Including a full M Code videos from basics to advanced. I have had very bad health for last year and the pace at which I can make MECS videos has slowed greatly. But I can not wait to get on more fun MECS videos...
@@excelisfun hope your health improved. Looking forward to your videos.
I'm on Team ExcellsFun, but for additional PQ videos, I've found these UA-cam channels insightful:
Goodly, BI Gorilla, AAA Excel’s Power, Access Analytic.
I'm getting pretty good with PQ, but am still very green with nested each functions where _ needs to come into play. Also being able to replace each and _ with a function using () and => is interesting.
@@bagnon Those are great PQ channels. I can't wait to post more PQ videos to help the Online PQ Team !!!!
@@bagnon thank you, I do watch Goodly and Access Analytic. They are great channels. With limited time it is hard to watch all the channels.
Thanks Mike!!! Good to know of other possibilities (Power Query). For me, formulas and conditional formatting is a better way. :) :)
Formulas Rule, right Formula Guy John? !!!!! : ) : )
@@excelisfun Yes Yes Yes Yes!!! :)
it's really sad how he has been uploading since almost 10 years but doesn't even have a million subscribers. you deserve so much more!!
Thank you for your kindness, Ashay!!! Although I have been uploading for over 14 years, I understand that the detailed videos I make with all the hows and whys are not what most people are looking for. Most searches on UA-cam just want a quick answer. That is not what I offer. Its all good, though : ) For those of us who want the hows and whys: we get to hang out and have fun : ) : ) : ) : )
Assuming the table is sorted by dates
=BYROW(N10#,LAMBDA(R,MAX(LEN(TEXTSPLIT(TEXTJOIN("",TRUE,IF(WeightsAN[Weight]=R,1,"|")),,"|",TRUE)))))
Thank you
you continuously add something new for me
Thanks for the formula, abubakr!!! Thanks for adding something new : ) However: Please post this formula solution below the video I post on Monday : ) : ) : ) We will have Team fun with " Max Consecutive Occurrences for Each Unique Number" on Monday. In addition, since the title of the Monday's video is " Max Consecutive Occurrences for Each Unique Number", when you post your formula solution there, anyone looking for this type of solution will find your great formula. Posting it here below this video has less utility.
I added your formula to download workbook.
@@excelisfun Thank you very much
This means a lot to me
@@abubakr5781 It is great being on the Team with you, abubakr!!!!!
Awesome 👍👍👍👍
Glad it is awesome for you!!!
Here is a Dynamic Array Formula video by Bhavya Gupta that accomplished the same goal as this Power Query video: ua-cam.com/video/JdbT9SnMmWU/v-deo.html
The pattern in this video is not "consecutive", it is really "repeated" numbers. Johnny Z pointed this out. I have been applying the wrong term ever since my first video on this topic in 2010...
The occurrence is consecutive positions of repetitive pattern are consecutive (occurrence refers to same objects by default, counts how an object is repeated) ✌😉
@@Excelambda See? I always tell you the truth about me ; ) I am slow and not that smart (but really hard working). Sometimes I can't tell if I am right or wrong, or just floating the gray area of most endeavors. But: I am lucky to be on a great Team!!!! Thanks for this clarification, my smart and helpful friend : ) : ) BUT... I did look up this word after Johnny Z pointed this out, but I could not find a definition or clarification for "consecutive" that confirmed that the way I was using it was OK. Your clarification here is REALLY helpful: positions of repetitive pattern are consecutive (occurrence refers to same objects by default, counts how an object is repeated).
Great videooo!! PQ is supeeer cool. ✌😉
No problem to do it with a formula also.
So, alternative solution to PQ:
=LET(t,Weights,h,3,
q,SEQUENCE(ROWS(t)),a,INDEX(t,q,2),
f,LAMBDA(x,CHOOSEROWS(x,-q)),
s,LAMBDA(x,SCAN(1,q,LAMBDA(v,i,IF(i=1,v,IF(INDEX(x,i-1)=INDEX(x,i),v+1,1))))),
y,s(a)+f(s(f(a)))-1,
FILTER(t,y>=h))
The master strikes again!!!! I just tried it with:
=LET(t,WeightsAn[Weight],h,3,
q,SEQUENCE(ROWS(t)),a,INDEX(t,q,2),
f,LAMBDA(x,CHOOSEROWS(x,-q)),
s,LAMBDA(x,SCAN(1,q,LAMBDA(v,i,IF(i=1,v,IF(INDEX(x,i-1)=INDEX(x,i),v+1,1))))),
y,s(a)+f(s(f(a)))-1,
FILTER(t,y>=h))
But got REF# error. Driver error, I am sure, however lol
@@excelisfun This is because "t" should be entire table, not only snd clm
=LET(t,WeightsAN,h,3.........
@@excelisfun Formula embeds same useful lambda that we did before for counting consecutive occurrences:
=LAMBDA(x,SCAN(1,q,LAMBDA(v,i,IF(i=1,v,IF(INDEX(x,i-1)=INDEX(x,i),v+1,1)))))
Thanks : ) I added whole table and : BAM! It worked : ) : )
@@excelisfun Best scenario, 2 useful functions for any other types of eventual filtering, at least n, btw a and b etc.
OCC(a) Occurrence Consecutive Count ( counts expandable range , like MOD creates patterns 1,1,2,3,1,1,2,3,4...)
=LAMBDA(a,SCAN(1,SEQUENCE(ROWS(a)),LAMBDA(v,i,IF(i=1,v,IF(INDEX(a,i-1)=INDEX(a,i),v+1,1)))))
TCC(a) Total Consecutive Count ( counts total consecutive streaks, like QUOTIENT creates 1,3,3,3,1,1,4,4,4,4,...)
=LAMBDA(a,LET(q,SEQUENCE(ROWS(a)),f,LAMBDA(x,CHOOSEROWS(x,-q)),OCC(a)+f(OCC(f(a)))-1))
And for the PQ alternative =>
=FILTER(Weights,TCC(Weights[Weight])>=3)
✌😉
This is Amazing Mike ... what is the best reference for Power Query M language ... any suggestion ?
None that I know of. I have a full M Code video coming up in MECS next year : )
@@excelisfun this will be awesome!!!
@@martyc5674 I can't wait. The whole MECS class has been what I have been wanting to do for a few years, but bad health for over a year has slowed the pace to a crawl... But I am like you: I can't wait : ) : )
@@excelisfun Mike you are a Saint!
@@martyc5674 Are angel's saints? Because my Mom named me after the angel Michael Anthony : )
Hi,
Is it rude to ask for a video?
I am looking to creat a depreciation schedule in power query.
I have a very large Capex data set.
I added the expected depreciation until end of useful life using sequence. This does work but its still quite sluggish. Also I need to recreat this process every once in a while and want it to be as automated as possible. I guess there are better solutions out there but I just can't seem to find one.
Anyways, thank you so much, even if this comment will go unnoticed, I'm just very appreciative of your work
No it is not rude. However, I have had very bad health (I posted about this) and am over a month behind in my posted classes. I can not make a video like this at this time. Sorry about this.
@@excelisfun
Thanks for the reply. Wishing you get better soon
1st comment
You get the first place trophy!!!! No tie this time, just a big trophy for you, Syed Hassan : ) : )
This is Try to Extract Records :
=DROP(UNIQUE(REDUCE("",SEQUENCE(ROWS(Weights)),
LAMBDA(o,s,LET(a,CHOOSEROWS(Weights,SEQUENCE(K5,,s)),e,TAKE(a,,1),z,DROP(a,,1),VSTACK(o,IF(IFERROR(AND((DROP(e,1)-1=DROP(e,-1))*(DROP(z,1)=DROP(z,-1))),),a,"")))))),1)
Yes!!!!!! Great formula : ) : ) I just added your formula to the download workbook file : ) Go Team!!!!
Why recording from home 😳.
All good?
Had home errands, but had a sudden set of ideas I just had to record. My tools are not quite as good at home. Thanks for asking, Viral Shah!!!! : )
Another one with SCAN
=LET(w,E10:E38,FILTER(D10:E38,
SCAN(0,SEQUENCE(ROWS(w)),LAMBDA(a,n,LET(v,INDEX(w,n),
IF(v=INDEX(VSTACK(0,w),n),a,XMATCH(0,N(DROP(VSTACK(w,0),n)=v))))))>=K5))
Amazing Excel Wizzard!!!! I will add, yet another one of your formulas to the download workbook : ) : ) Go Team!!!!
@@excelisfun Thanks, Mike 🥰
Great use of GropKind.Local 👍👍
My 2 attempts with MAP
=LET(z,Weights,w,DROP(z,,1),
FILTER(z,MAP(w,LAMBDA(x,LET(n,ROWS(E10:x),v,N(VSTACK(w,0)=x),
XMATCH(0,DROP(v,n))+n-IFNA(XMATCH(0,TAKE(v,n),,-1),0)>K5)))))
=LET(z,Weights,w,DROP(z,,1),
FILTER(z,MAP(SEQUENCE(ROWS(z)),LAMBDA(n,LET(x,INDEX(w,n),v,N(VSTACK(w,0)=x),
XMATCH(0,DROP(v,n))+n-IFNA(XMATCH(0,TAKE(v,n),,-1),0)>K5)))))
My attempt with REDUCE
=LET(z,Weights,w,DROP(z,,1),DROP(REDUCE(0,w,LAMBDA(a,x,
LET(n,ROWS(E10:x),c,XMATCH(0,N(DROP(VSTACK(w,0),n)=x)),
IF((xOFFSET(x,-1,))*(c>=K5),VSTACK(a,TAKE(DROP(Weights,n-1),c)),a)))),1))
Thanks for your amazingly smart formulas, Excel Wizard : ) : )
I have added them to download file. However, I could only get the second one to work.
I think Weights refer to a different sheet, please try this
=LET(z,D10:E38,w,DROP(z,,1),
FILTER(z,MAP(w,LAMBDA(x,LET(n,ROWS(E10:x),v,N(VSTACK(w,0)=x),
XMATCH(0,DROP(v,n))+n-IFNA(XMATCH(0,TAKE(v,n),,-1),0)>K5)))))
=LET(z,D10:E38,w,DROP(z,,1),DROP(REDUCE(0,w,LAMBDA(a,x,
LET(n,ROWS(E10:x),c,XMATCH(0,N(DROP(VSTACK(w,0),n)=x)),
IF((xOFFSET(x,-1,))*(c>=K5),VSTACK(a,TAKE(DROP(z,n-1),c)),a)))),1))
@@ExcelWizard Very good : ) They are all working in the download workbook!!!!
@@excelisfun great, thank you. Mike 😍
@@ExcelWizard You are welcome AND Thank You, Excel Wizard : ) : ) Go Amazing Team!!!!