MSPTDA 08: Power Query Group By feature & Table.Group Function (5 Examples)

MSPTDA 08: Power Query Group By feature & Table.Group Function (5 Examples)


82 Replies to “MSPTDA 08: Power Query Group By feature & Table.Group Function (5 Examples)”

  1. Been watching this series since the beginning and thanks for all the hard work and nice tips. Always learning something from your video tutorials.

    Since you brought up the idea of nested data column, just wondering if you could generate a column with Moving Annual Total Sales for each month-product combination. For example, Product A's Fiscal YTD Sales-12 months- $100K in July-2018 and $95K in June, $98K in May, etc…

    I know we could use DAX time intelligence function like DATESINPERIOD but just wonder if this could be done for raw data with PQ. The idea is to calculate inventory turnover for each product before uploading data to Data Model so DAX engine doesn't have to deal with the heavy calculation.

  2. This is really awesome. So beautifully explained the concept. Thank you so much Mike. Awaiting with bated breath the next video in this series.

  3. Another great video from mike, thanks a lot, i have a question:
    when we select close and load to and only create connection option for the first time, then we can't select close and load to table after that and this button become deactivate, is there any way to select this option after selecting only create connection?

  4. Wow, simply amazing Mike, I loved the part when you did all the steps in single line of code πŸ™‚ & MSPTDA is great & the best course on power query.

  5. Thanks Mike, great video. I have an unrelated question. I have some quite complex queries that run over a lot of files and sometimes they can be very slow. Do you have any suggestions for what can slow down Power Query?

  6. Text.Combine Are you kidding me? I have been trying to figure that out in Access forever. This is making me re-evaluate my work flow processes. Thanks Mike

  7. When we have teachers like you… There is absolutely no need of anyyyyy books..and also be the class topper…

    Lately i am doing lots of Rstudio… Only to realise the quality of ur teaching and explanations are beyond gold treasures.. None of the other channels are even close to ur quality and content..

  8. Hi Mike, thanks for another great video.
    Will you be able to use/open a file with power query on a Mac excel?

  9. thank you so much Mr. Mike this is an awesome series to me because it's full of new things thank you again

  10. Thanks Mike, great video. One thing: I never saw what you did at the end: copy data (non table) to the first data cell of that table. I could not see the total result of this on screen but you said: "I would like to add…(28:55)", later you say: "new records". So I was not sure if it was replacing or adding. If I follow your instructions, after ctrl-v the data is replaced, not added. So replacing was the intention, or did I do something different?

  11. Hi Mike. Just last week I had a project with an ungodly amount of unpivoting. I would generally do this in SQL, but was able to do it in seconds via PQ. Just wanted to thank you again for your awesomeness.

  12. World Class Series, Mike, beautiful and instructive.
    Also, thanks for pointing out that although the PQ interface is really helpful and lowers the M-learning curve by a lot, it is not perfect and most definately not complete. So, the place-holder-swap technique comes in handy to deal with the obligatory syntax constraint.
    And finally: Yes, the MS taxonomy of M-functions is a good browser short cut to have at hand.
    See you on the next one.

  13. SHOWWWWWW, M Language is amazing !!!!! Your files are amazing, thank you.

    I uncheck "Automatically detect column type" in Power Query Options.

    Ken Puls made a great post about rankings in PQ:
    https://www.excelguru.ca/blog/2018/06/14/ranking-method-choices-in-power-query/

  14. Great tutorial once more. Thank you so much for the cristal clear paths to solutions. Ranking with group by is a cool idea!

  15. HI, Thank for great videos.
    I have a problem if you can help, below table i need to find difference in amount between two lists but second list sometimes has a sum of values:
    Date List 1 List 2
    01.09.2018 45000 45000
    02.09.2018 30000 30000
    03.09.2018 25000
    04.09.2018 32000
    05.09.2018 30000 87000
    06.09.2018 2800 2800
    07.09.2018 45000 45000
    08.09.2018 2800
    09.09.2018 65000
    10.09.2018 75000 142800
    11.09.2018 32000 32000
    how to make a query that check if the difference is sum of record and finds the difference

  16. Hey Mike, Great contents πŸ™‚ I've been studying your contents for years πŸ™‚
    Quick questions for this e.g. How do you interpret the Standard Deviation in this sample?
    E.g. for Total Sales for Quad is 6,658 and the Standard Deviation is 821.26 … How do I interpret the 821.26?
    Many thanks πŸ™‚

  17. Thanks, Mike. It's very nice to know Power Query can edit table inside table with Add Custom Column or by Grouping. We can access cell inside a column in table by Table.AddColumn(#"Grouped Rows", "FirstCell", each [All Rows][Index]{0})

  18. bro i have a question how can i sum and average at a same time in group by function i want to share a table with you to explain my question so how can i share with you

  19. Mike, wow! What an excellent video!
    1. When I saw the title Group By, I was thinking of skipping the entire video as there is not much new that can be said about group by – that is what I thought. Then, I checked out the notes and found the mention of 'All Rows' intriguing. It has been a few years since I have first tried PQ when it was an add-in for Excel 2013. I have never realized that I could programatically manipulate the filtered records so easily in PQ. So, I decided to watch the video – Another wow! What a wonderful set of examples that made me feel "I need to really try this to really understand this at a deeper level". I am amazed at ingenious design of the concept of a cell in PQ that it can hold a list or table and we can recursively work on it – This is the first material new info for me about PQ in these many years, except for what I have learnt on my own by exploration. And you sparked my curiosity to learn 'M' which so far I ignored as I felt that it would not give me enough ROI for time invested in learning it, given that so much is possible through mouse-clicks!

    2. Returning a comma-separated list of unique values is a very hard job in any BI tool. Glad to have CONCATENATEX in DAX and List.Combine in PQ – Wow
    !

  20. Mike, when I was your explanation of Group by, I felt that I should share with you 2 videos I created on my channel to explain how aggregation works in case of Text/Numbers. I explained it for Tableau audience, but the concept should be applicable/understandable to Excel users also. Would be glad to have your comments on them, if you have time
    https://www.youtube.com/watch?v=PqZpm04ATo4
    https://www.youtube.com/watch?v=heS7G1t65jo

  21. Thank you very much again for sharing your knowledge…
    I loved to read comments on how people appreciate the work. It means a lot to all the people…(but still, asshols are there who dislike)….Β The amazing this is you find time to respond to all most all the comments…. Respect you!

  22. Thank you for these amazing presentations!Β Β An interesting challenge for anyone:GroupBy Minimum in a new column "min_Group" but retain all other original rows and enter NULL in column "min_Group". There must be a better way but this is how I managed it: created "unique_KEY" in original table, created second table with Minimum per Group, then merged both tables by "unique_KEY".Works, but there has to be a faster method using only one table. I guess creating a function column?Β  Thanks!

  23. i am addicted of ur videos…the way u teach r mindblowing…great sir keep it up..please if possible make vba in excel video..

  24. These videos are amazing, love how they cater for total newbies like me and for more advanced users. The explanations on the coding are particularly useful πŸ™‚

  25. Hi, Mike. I have a question,suppose i have list of deposited amount of several customers.For example, customer A total deposit amount is 10.he has deposited 10 by (2+2+1+1+2+2=10). if i use list.distinct function it will return only {2,1}.but i want to show all value such as {2,2,1,1,2,2}.so for that which function i should use?

  26. Mike, I cannot find words of gratitude, thank you so much!!!!, you are the best for sure, you strategically breakdown complex tasks into manageable steps and later when all the steps are aggregated we are already mastering it. Thank you again, you are definitely contributing to the development of human capital all over the world. By the way, I am waiting for your next book !!!!!!

  27. Great session! Getting a first look at list. function is very powerful. I really liked the last one where you added the placeholder for standard deviation. Off to MSPTDA09!

  28. Learnt so much new in just 30 minutes. Never knew power query could do such much.

    A big thank you and thumbs up from Pakistan. πŸ™‚

  29. Thanks Mike. πŸ‘ The 'all rows' functionality in the group by area in power query is exactly the same as the drill down function in pivot tables…

Leave a Reply

Your email address will not be published. Required fields are marked *