MSPTDA 08.5: Power Query Group By Unique List or Consecutive Occurrences

Download Excel START Files: people.highlin...
Download Excel FINISHED Files: people.highlin...
Download pdf Notes about Power Query: people.highlin...
Assigned Homework:
Download Excel File with Homework: people.highlin...
Example of Finished Homework: people.highlin...
In this Video learn how to use Power Query’s Group By feature to Group By and create a unique list with aggregate calculations or create a Group By Report based on Consecutive Occurrences of items in a given column with aggregate calculations.
Topics:
1. (00:15) Introduction
2. (00:37) What is Group By Report based on Consecutive Occurrences?
3. (01:27) Group By feature to Group By and create a unique list with aggregate calculations
4. (03:15) Learn about how Gear Icon can Disappear when you alter the M Code, which means the dialog box disappears.
5. (05:12) Learn about the difference between Duplicating a Query and Referencing a Query.
6. (05:12) Group By Report based on Consecutive Occurrences of items in a given column with aggregate calculations. Use the forth argument and GroupKind.Local
7. (07:27) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

[@barttitulaerexcelbart9400]

Global, Local, strange names, but definitively helpful. I presume it is important that the list is sorted on the column date. If you are not sure if you import a lot of data, you should add this step. But in your solutions you try to keep the number of steps as short as possible. And that's fine too !! Looking forward to MSPTDA 9,0

[@excelisfun]

Yes, it is important to sort. Maybe I should have mentioned that, but I assumed Consecutive Occurrence implied a certain sort. Without a doubt I should have mentioned that.

[@excelisfun]

I added a pinned note about the sort. Thanks for helping me to edit, Bart : )

[@excelisfun]

Consecutive Occurrence implies a certain sort. So the Group By for Consecutive Occurrence requires that you sort the column the way you want it before doing the Group By GroupKind.Local. You can add a step in the query to assure the correct order by sorting the Date Column.

[@Victor-ol1lo]

Wow....Consecutive grouping ... Great Trick. Thanks Mike !!

[@shoeshines2121]

Thank you for breaking down the default grouping type (Global - Unique List) and the alternative grouping type (Local - Consecutive Occurrences). The Grouping videos were just so thorough and detailed - great tutorial.

[@JSUG4219]

Mike, this whole MSPTDA series is incredible! You have me on the edge of my seat waiting for more!

[@excelisfun]

Glad it is incredible for you! Thankss very much for the support with your comment, Thumbs Up and Sub !!!! The next video is taking longer than most, and I will still not have the next video until a few more days, hopefully Sunday.. The next video is just huge and epic and all about M Code... I an on the edge of my seat too : )

[@hcbrasileiro]

This is totally what I was looking for! Thank you so much. Just found your Channel, it'a amazing. Congrats

[@excelisfun]

Yes, it is a big archive of stuff. Glad you found it and glad this video helped : )

[@petermyran4986]

Thanks Mike for the thoughtful homeworks on all of these sessions. Makes me think beyond just taking in what you are teaching us in the videos. The min max on dates was a good conceptual addon for the Win / Loss consecutive 'group by'. Grateful

[@excelisfun]

Yes, the homework is very important - and fun!!! Thanks for your consistent support : )

[@masterof]

Thank you Mike. I really wish PowerQuery offers command hints like in the Excel formula bar.

[@excelisfun]

I wish so too!!!!! It is quite hard to learn and use Power Query sometimes. But... It is still so amazing : ) Thanks for your support, rf05mjy!!!!

[@ExceliAdam]

Power Query how so much I didn't learn yet. Thanks for teaching me this!

[@excelisfun]

Yes, we have a great online Excel / Power Query Team and so we can all learn from each other! Go Team!!!!!!!

[@kosalanef]

Thank you very much. The homework was tricky. Really helps to think in different ways and to learn how to apply these techniques. Thank you

[@excelisfun]

You are welcome very much, Asantha! Glad it was tricky, but informative and fun!

[@march152006]

Thanks a lot, Mike. Amazing training videos and all that for free.

[@excelisfun]

You are welcome, Alireza!!!! But it is NOT for free!!! I change a comment and thumbs up for each video that you watch ; )

[@mattschoular8844]

Good to know. This aggregation type could be applied in so many ways. Thanks Mike....

[@excelisfun]

Yes, indeed, it is a really useful tip that seems like they made it hard to use... Thanks for your support, Matt!

[@OzduSoleilDATA]

HOLY SMOKESSSSSSSSSS!!!!!! 🔥🔥🔥🔥🔥

[@excelisfun]

Fun with Power Query !!!!!!!! Thanks for the support, OZ!!!

[@brij26579]

Awesome liked consecutive Occurance approach 👌much needed 👍

[@mohamednofal4725]

As usual, no words enough to show my appreciation. Thanks a million.

[@excelisfun]

Thanks for the millions, mohamed : ) : )

[@jazzista1967]

Great example Mike. I was not aware at all of this great GLOBAL/LOCAL capability in power query. I will start using this feature in my financial reports at work. Thanks for the great enlighten of all your videos!

[@excelisfun]

You are welcome! And... there is so much more to come! The next videow ill be one of the most epic videos I have ever done - all about M Code. Thanks for your consistent support : )

[@SyedMuzammilMahasanShahi]

Thanks Mike for this EXCELlent video.

[@excelisfun]

You are welcome, Syed!!!! Thanks for your support : )

[@ceyhunozturk5115]

Much Appreciated Mike.

[@excelisfun]

Glad you like these MSPTDA videos, Ceyhun!!!!

[@Luciano_mp]

Mais uma excelente dica na forma como podemos agrupar os dados, muito bom, falar que você é o cara ficou redundante. Obrigado Mike.

[@excelisfun]

Não redundante - agradeço as palavras amáveis ​​!!! Obrigado pelo seu apoio, Luciano : ) : ) Not redundant - thank you kind words !!! Thanks for your support, Luciano : ) : )

[@mahmoudaboelazam8068]

جزيت خيرا

[@pmsocho]

Thanks for the video. GroupKind.Local has already helped me many times! Thanks for showing it to the public!

[@BillSzysz1]

Thanks :-)))

[@excelisfun]

You are welcome, Teammate and Poet of the Highest Magnitude Bill Szysz : )

[@mohitmanwani5298]

SPLENDID MIKE..I AM IN LOVE WITH YOUR VIDEOS>>>MY JOB AT WORK WILL BE CAKEWALK NOW!!

[@vijaysahal4556]

sir I have no word what say about you bcoz super, superb super dupper 👍👍👍

[@excelisfun]

Glad I can help you learn, vijay!!!! Please help me and support the free resources that I post with a comment and thumbs up on every video that you watch : )

[@vijaysahal4556]

@@excelisfun offcurse sir

[@vijaysahal4556]

@@excelisfunbefore your teaching power query video I dnt know what is power query &what is powe pivot table m start 0 leable with you really thanks to you

[@excelisfun]

@@vijaysahal4556 Thank you , vijay!!!!!

[@alirezamogharabi8733]

Thank you so much for this great video.

[@excelisfun]

You are welcome, so much, ali!!!! Thanks for the support : )

[@alirezamogharabi8733]

ExcelIsFun Excel community need more and more contents and resources about M language, thank you Mike.

[@excelisfun]

I have a lot more coming!!! It will be great fun : )

[@alirezamogharabi8733]

ExcelIsFun Thank you very much for your help and efforts.

[@峰王]

Thank sir!

[@joaquimcosta952]

This function is very very usefully. Thanks.

[@excelisfun]

You are welcome!

[@mazarata73]

Great tutorial. Thanks a lot for this very well explained insight.

[@excelisfun]

You are welcome, Giuseppe!!!! Thanks you for the support with your comment, Thumbs Up and Sub : )

[@alialbayati9368]

Thanks, Mike. Amazing as usual.

[@excelisfun]

You are welcome, as usual, Ali : ) Thanks for the support!!!

[@cristian.angyal]

GroupKind. Local is new for me ... thanks for adding a new video on Grouping to show it!

[@excelisfun]

You are welcome! Thanks for the support, Cristian : )

[@kiranbangar]

Awesome I am new in power query and it’s awesome 😮

[@excelisfun]

Glad the classes help : ) : )

[@tableaupro3233]

Mike, I finally found this missing video between 8 & 9 that explains the consecutive occurences. A very nice trick indeed - but I can't recall/visualize any real-life problem where I can apply this idea. Do you have any real-life scenarios where this could be helpful?

[@excelisfun]

Football teams appearing in consecutive bowl games, survey questions with consecutive answers, and many more situations where we are trying to recognize sequential patterns. I remember BEFORE Power Query seeing this "how many happen consecutively?" question asked occasionally at the mrexcel.com/forum Message Board and the crazy array formulas we used to use with the FREQUENCY Array Function. I even had an example in the book I wrote about Array Formulas. So this method with Power Query is a relief because it is so much easier than the way we used to do it...

[@tableaupro3233]

@@excelisfun Thank you Mike. When I attempted the assignment, I realized that the winning streak is a great example of this. Thank you very much Mike.

[@adamshrinah2595]

Thank you

[@excelisfun]

You are welcome!

[@jonywu2259]

Your video is really helpful, thank you so much

[@excelisfun]

You are welcome so much!

[@DougHExcel]

What a neat feature! Thanks for the video!

[@excelisfun]

You are welcome, Doug! Go Team!

[@hamidbahari6588]

Thanks Mike!

[@excelisfun]

You are welcome, hamid!!! Thank you for your support : )

[@GeertDelmulle]

Nice extension of the Group By function - good to know.

[@excelisfun]

Glad it helped!

[@filipsvakjaroski20]

Thank you Mike :)

[@excelisfun]

You are welcome, Sir : )

[@ExactProBi]

Awesome tip, thanks a lot Mike, is consecutive grouping done based on the order of data in TypeDay column? so if we choose Data to group by, it should group consecutively based on dates occurrence order?

[@excelisfun]

It is dependent on the sort of the column TypeDay. I should have mentioned that....

[@ZAHIDHUSSAIN-ri5kg]

Many, many thanks Mike!

[@excelisfun]

Many, many you are welcomes, ZAHID!!!! Thanks for your support with your comment, Thumbs Up an Bell Sub : )

[@FRANKWHITE1996]

Thanks for the video!! 😍

[@excelisfun]

You are welcome, Sir!!!

[@DIGITAL_COOKING]

great mike!!!....., not just for the videos but also for the way you work like using the shortcuts because I start to use them .. I don't know !!! it speeds up thumbs up

[@excelisfun]

Keyboards are THE trick to efficiency. I occasionally get comments where people are angry that I teach all keyboards. But the thing is, that it is my duty as a teacher to try and teach the most efficient way - and so it is my duty to teach that way. Glad you are getting the keyboards, DIGITAL COOKING!!! Thanks for your support!!!

[@stevennye5075]

Excellent!

[@excelisfun]

Yes, Steven Nye!!!! Group By is power : )

[@stevennye5075]

well done!

[@himanshudalai1028]

Thank you Mike. This is awesome.

[@excelisfun]

You are welcome, HIMANSHU!!! Thanks for your support : )

[@ismailismaili0071]

this is really awesome thank you so much Mr. Mike

[@excelisfun]

You are welcome so much, Ismail!!! Thanks for your consistent support : )

[@stevennye5075]

very informative

[@excelisfun]

Glad it helps! Thanks for your support, Steven!

[@MySpreadsheetLab]

WOW! Amazing M tip! Thanks Mike!

[@excelisfun]

You are welcome, Kevin! Many more fun M Tips in the next videos : ) : )

[@chrism9037]

Great video, thanks Mike!

[@excelisfun]

You are welcome, Chris!! Thanks for the amazing support : )

[@qianyu711]

This is AMAZING. THANKYOU!

[@vida1719]

Interesting function. Hope to use one day in future

[@excelisfun]

Maybe you will have an opportunity to use it : ) Thanks the the support : )

[@sanukumar7122]

Thanks sir

[@kamranb1369]

This is sweet :-) Thanks, Mike

[@excelisfun]

Sweet like a bowl of sugar : ) Thanks for the support, K B!!!

[@johnborg5419]

Thanks Mike :)

[@excelisfun]

You are welcome, John "Love Those Array Formulas" Borg : ) : ) Thanks for the support!!!

[@johnborg5419]

Yes, That's for sure, Array Formulas are Great. Thanks to you. I downloaded the 08 and could not resist to try and playing with formulas too besides practicing with Power Query. Thanks Mike :) :)

[@dorissweanapo9860]

I was waiting for this video :)

[@excelisfun]

Glad it got to you, Doris!!!! Much more fun to come : ) Thanks for your amazing support : )

[@mohamedchakroun4973]

Absolutaly Amazing :-)

[@excelisfun]

G;ad it is amazing for you, Mohamed : ) Thanks for the support!!!

[@Apatchi92]

GREAT

[@excelisfun]

Glad it is great for you!!!!

[@anand9985]

Nice sir

[@excelisfun]

Glad it is nice for you, Dayanand!!! Thank you for the support : )

[@davebowman5392]

Thanks Mike

[@excelisfun]

You are welcome, Dave: ) Thanks for your support !!!

[@wmfexcel]

There are so much to explore about M code @ExcelIsFun :)

[@excelisfun]

Yes, indeed, and the next video will be the most epic one in the series so far : ) Thanks for the support, MF Wong : )

[@hosseinhosseinpoor9561]

good

[@rjbush7955]

Thanks Mike. The PDF notes above; are they for MSPDTA 8 only or do they include notes for MSPDTA 8.5 as well?

[@excelisfun]

Yes, they do. The notes about 8.5 are the last three sections. Thanks for your support, RJ : )

[@rjbush7955]

Thanks again. I'm getting pretty good with PQ (thanks to your help). Looking forward episode 9 and getting dirty with M code.

[@pantrycoupon7936]

Mike in the homework a how did you group the date range Thanks

[@regisaguas8579]

Great vid

[@excelisfun]

Glad you liked it! Thanks for your support, regis : )

[@rrrprogram8667]

Mike...... is there any possible option that I do all the data wrangling in power query and somehow import that final table into R ?? or any other lang Python?? Can we do that?

[@excelisfun]

I do not know, but I bet there is a way : ) Anyone else know?

[@MalinaC]

I didn't the GroupKind feature. Thanks :)

[@excelisfun]

You are welcome, Teammate : )

[@MalinaC]

:)

[@alwarhi]

Great

[@excelisfun]

Glad it is great for you : ) Thanks for your support!!!

[@suneelkokate5210]

Hi ExcelIsFun Can you upload any training video that discusses about Call Center Reporting Inside out. (AHT, Attrition, Shrinkage, Cost per Agent, Agent Job Satisfaction.) I mean Everything about Call center Reporting. Thanks.

[@excelisfun]

No. I do not know anything about that. I am sorry.

[@suneelkokate5210]

Thanks, for honest and quick reply. I will try to find it with other online resources. But I will keep watching your videos that teach too many other useful techniques.

[@excelisfun]

Thank you very much for your support : )

[@alexj8117]

Thanks Mike. I followed up in the M formula guide and found a final parameter for Table.Group called "optional comparer". Do you know how this would be used?

[@excelisfun]

I have no idea. I searched for an example and found none. The help says; "An optional argument that determines equality between group keys". I guess it means if you are grouping by more than one column and the two elements are equal!?!? If you find something, or if anyone else finds something, please post back and help our Team : )

[@alexj8117]

Maybe we need to cal Bill Syzyz?

[@DIGITAL_COOKING]

can you help me with this problem i have this formula in power query to change value for null = Table.ReplaceValue(#"Replaced Value3",16,null,Replacer.ReplaceValue,{"Réf Tiers - Copy"}) and i have other nubers (like 1, 2, 10) to change them too the question is : can i do it with the M code in one step

[@excelisfun]

I do not see a way to use the Replace Feature to search for all numbers. Maybe a formula in a Custom Column, like this: if Value.Is([Column], Int64.Type) then null else [Column] The full formula would be: = Table.AddColumn(#"Changed Type", "Custom", each if Value.Is([Column], Int64.Type) then null else [Column])

[@DIGITAL_COOKING]

this is what I was looking for and it works just fine, mike you are EXCEL WIZARD

[@excelisfun]

Yes!!!!!

[@ThatBoyLegend]

don't understand the homework, any help anyone?

[@Muuip]

My challenge for today: Retain information of 30 columns, groupby 4 columns, Filter by smallest value of 5th column (filter smallest value of each unique 4 columns group)

[@rrrprogram8667]

Mike .. I suggest you to move all this series to Udemy or Edx or some other online learning platform.... course fee of minimum value 5$ to 10$ a year..... believe me, only then people will understand the value of this kind of FREE Education..... Post only intro videos on youtube and rest intermediary and expert level on online... I guess 10$ a year is like a drop of sea water for a worth of actual sea

[@MalinaC]

I definately agree!

[@excelisfun]

RRR Program, I 100% will not do that! My whole mission in life over the past 10 years here at KZbin is to provide education at KZbin at no cost. If people do not value it because it is here for free at KZbin - they do NOT have to watch it. If it ends up that NOT enough people watch what I post here at KZbin, then I will close the channel and do other fun things in life : )

[@excelisfun]

Malina c., I 100% do not agree! I posted what I think in the above comment, but in brief: my whole mission in life for the past 10 years here at KZbin is to provide the knowledge that I have for free. If humans do not value what I do because I do not charge money, then I dare those humans to break that pattern and watch and learn for free : )

[@MalinaC]

No! Never close Your Channel! Thera are still people who values that. A LOT!!! :)

[@excelisfun]

Hopefully not.

[@NoShadowOfDoubt1]

Ctrl+alt+F5 ....

[@excelisfun]

Yes, keyboards rock! Thanks for your No Shadow Of Doubt Support : )

[@cmaman1]

Thank you

[@excelisfun]

You are welcome, M. Alomery!!!!

[@sadyaz64]

Another great vidéo thanks

[@excelisfun]

Another great "You are welcome"!!! and thanks for the support, sadyaz64 : )