Unleash The Potential of Group By With All Rows

[Music] [Music] hey everybody i want to take some time today and show you what i think is the most underrated feature in power bi it's the group by function in power query and it's this incredibly powerful capability that lets you solve some very common problems in a really simple way and so i have a couple of examples from the forum recently that i want to walk through and the first one is from enterprise dna member pete and he was having some dax problems with a with a group in question summarize columns he was getting some circular redundancy having some problem with filter context quite a complex thing to work out and so what i did is took a look at the problem and suggested we we go a completely different route which is instead of dax let's take a look at this group by function and it's one of those things that's always a useful candidate when you've got a series of attributes that you want to group on and then you want to create an aggregate so a sum a min a max a median kind of a whole range of different different aggregate functions similar to what you would do in dax and in this case what he wanted to do was group by customer and then by year and month and then sum his sales amount and then put that that sum on each row of the of the group so here we've got customer b january 2020 46 and 43 dollars so total of 89 and then put that 89 on both rows so let's let's jump into power query and take a look at how this works so when i use group by what i do is typically jump right into the advanced formulation it's not any harder to use and i feel like it's just a little more intuitively laid out so what we want to do is we want to group on customer and then we want to group on year and month and then this is total sales amount grouped and we want to do a sum here of our sales amount column and if we click ok what we get is not exactly what we wanted um because if you remember what we've got here is we've got unique rows of customer year and month and then the total but we only have one row per customer so it's taken the it's taken the sum for each customer um but if you remember what we wanted was we wanted to it to return each of the customer rows and then the total on each row and so this doesn't get us exactly what we what we wanted and so let's let's jump back into power query and see how we can fix this so if we go back to the grouping if we look here at aggregation and add aggregation and let's just for now i'm just going to call this all data for reasons we'll see in a minute if we look at the the aggregates that are available there's some there's average median min max count rows those are all pretty normal and there's one weird one sitting down here at the bottom called all rows and that is the one that is going to solve our problem and really kind of supercharge this function so if we select all rows what this does is this provides a way to return all the rows that were in the original data with the aggregate operator applied to it and let's just move this up and now let's rerun it and we can delete this because it's going to be included in our in our nested table so we just remove these columns and then we expand here and boom we've got we wanted which is for each each customer and then each total sales amount it returns that total sales on each of the grouped rows so that is exactly what we were looking for and you can see that you know instead of complex stacks and circular references it's literally just a minute or two to put that together in the in the group by function so now that i've made quick work of this problem i want to show you one that's a little bit tougher but still illustrates well how the the group by function and the all rows can really help us and so this is one from ankit and i can tell from looking at this data that he's a chicago bulls fan these are the these are the bulls uh finals records um in the 90s the great michael jordan teams and what he wants to do is he wants these are these are best of seven game series in each of these in each of these years and what he wants to do is to determine how many series um they won that at least you know they won four games out of seven or four out of um the the total play to get to a series win and then count the number of series wins and what i did is actually change this data just to show you that the the functions working well because they ended up winning all six of these series so i put some losses in here so that they won four series and lost two just so you can see the functions working right and this is again this this one a number of us jumped on this one when uh ankit posted this in the forum and i'll show you um melissa de corte's solution which are always great and this one in particular was we'll close and apply and get out of power query for the moment and i'll show you she um interestingly despite being an incredible expert in power query um did this one in dax and um wrote some really gorgeous tax code let me show you the measure here and you can see this is a pretty complex pretty complex formulation we've got nested filter commands we've got nested iterators we've got a generate and values combination two earliers in the in the filter context and so if you if you can put this kind of dax together um you probably don't need to be watching this video and if you can put this kind of dax together in a couple of minutes faster than we can do it in in power query i definitely want to be watching your videos so let's take a look at how we would go about breaking this down instead of through dax doing it in in power query so we'll jump into power query here and the first thing we're going to do is we're going to take these these win losses and we're going to convert these into a count measure and we'll do that by add columns and then column from example we could do it conditional we could do it through m um but this is a a really simple straightforward way to do it and you'll see you'll see in a minute where this is going to lead us so we've got losses that we want to assign a zero value to so we just go 0 wins we want to assign a 1 to and it'll just take us a couple of entries here until power chord picks up the pattern and there we go so we've got we've got losses or zeros winds are one and we hit okay and now the next thing we want to do is we want to figure out what the deciding game in each series is and in a series like this the deciding game is always going to be the last one played whether that's a winner or loss that's the one that ends the series and so what we'll do is we'll go back into our our group by go to advanced group by year and then we'll start off with our all data all rows trick and find it at the bottom and now we're going to add an aggregation where we're going to we're going to call this deciding game and what this is going to be is it's going to be the maximum of the games played in that series and we click ok and we expand out we don't need year because we've already got that sitting here we could have deleted that or we can take it out here so it doesn't duplicate but either way we now expand out and what we've got is we have got for each series it tells us which the the deciding game is so the last thing we want to do is we want to create another column that just returns the the value of this count column for the deciding game of the of the series and we can do that through a conditional column and we can call this series win count let's fix that real quick okay and what this is going to do is this is going to say if the um game is the deciding game then we want to return the value of our count column and if not we just want to return a zero and we click ok and there we go so we can now go home close and apply and our work is almost entirely done here so this is the table that was created through the complex measure that we talked about before and we want to recreate that using the the analysis that we just did so let's take year and pop that in over here turn it into a table make sure it doesn't summarize and then we're going to take our series win count and we're going to drop that into the table and oh one thing we've got to make sure is we've got to make sure that that is not taxed that that is in fact a whole number and that's something we should have checked in power query before we left but we can do it here as well and so all we need to do at this point is we could do a very simple measure that just sums the the wind count it's the simplest possible measure but since i said we weren't going to do any dax i'm going to do something that i don't typically do which is i'm going to use quick measures which i don't like but i want to kind of prove a point here that we can we can do this with with no dax at all so if we take and we sum that series win count you see that we get the exact same result and we do with absolutely no dax and really quite quickly if you if you go through start to finish on that that group by it's really just a couple of minutes so you can see through these two examples the power of group by and particularly when you combine it with the ability to either return the aggregate or return all rows it gives you remarkable flexibility to tackle a wide range of group pi problems so i hope you keep that in mind when you start to see grouping of attributes in an aggregation give that a try i think you'll find it's a really valuable tool so i hope you found that useful as always thanks for watching and we'll see in the next video hey everyone thanks for tuning in to enterprise dna tv if you enjoyed the content covered in this particular tutorial please throw the video a like it really helps us and we really appreciate it also don't forget to subscribe to the enterprise dna tv channel we have a huge amount of content coming out all the time from myself and a range of content creators all dedicated to improving the way that you use power bi and the power platform lastly check out enterprise dna's website plenty of resources and further learning that you can access very easily all the best take you

Channel: Enterprise DNA

Views: 2,952

Rating: 5 out of 5

Keywords: Power BI, Power BI DAX, Enterprise DNA, Power BI Tutorial, Power BI tutorial for beginners, power BI desktop, power query for power bi, learn power bi, learn Power Query, M language, Power Query M code, group by power query, Group by power bi

Id: ni2ykB4XoIs

Length: 15min 10sec (910 seconds)

Published: Mon Mar 22 2021