Grouping Data with Power Query

hi this will show you how to group data in power query so what does this mean let's say for example we have a table like this this is a five column table it's a properly set table and what we want to do is just group some data let's say for example we want to group data based on maybe Group A or the the grouping that these items show up in and we want to see those items and we want to see the quantity and we want to see regions now I'm going to kind of compare this what grouping within pivot tables so if you're familiar with a pivot table you probably know that you can also group data so we have this group grouping here which is very similar to this we have this item here which is very similar to this and we have the sum of quantity which is very similar to this column see here but one difference is that with grouping in power query you can do a lot more things in terms of getting the information out of this specific row of data so I'm going to show you what that means when I bring this up and show the example so in this particular example where we have power query we could also show the regions that comprise of this row so in Group A item two with a quantity of five we can say Oh which regions are part of this five of this group a item two of this five they're the north and west regions now in a pivot table we're not really able to do that and create a separate column for that now if you double click the sum here you can actually get a separate table that has this information but in a table form whereas in power query we have a separate column that brings out the details of the items that make up this specific row this group a item to this quantity of five so let me go ahead and show you how this is done and you can get a better view of what I mean so here we are in Excel and I've got my table set up here we've got my date my item my grouping my region my quantity and what I need to do is I want to go ahead and have power query tab available now power query is an add-on in Excel 2010 and Excel version 2013 where you have to download it and enable it you can just go ahead and Google power kori Excel download and you'll find instructions from Microsoft comm to download it and enable it but I've got it enabled here I'll go ahead and and click on power query tab here and what I'm going to do is I want to get my data from the table so this is a table right here I can click anywhere with this in the cell and click from table and it's going to bring up a separate window where which is the power query editor now you can see that it's actually brought up the that range of data already and I don't really need to do too much in terms of editing the data types here because they brought it in from Excel now if you had data that's coming from another source and you weren't too sure of the data types you can also change the data type so I clicked in this column you can see here my I'm in my home tab here under the transform it is selected the data date and time which this is and these items here these are text so it's brought them in aesthetics you can see that if I click on here it's brought in it in this text this last column is a number so if I click on that you notice that it's brought that in this number so I don't need to worry too much about that but what I want to do now is I want to go ahead and group by the item and this group so I can do is I can select this press the ctrl or shift key I'll just use the ctrl key size multi-select I can click on those two and I'm going to go ahead and under the Home tab here I'm a click group by it's going to come up with another window and it's asking what I'm going to group by so it's already selected they're here that particular column these two last second column and the third column it's brought it in here now if I didn't select that I can also add it here so let me go ahead and show you what that does if I just select it if I didn't select anything let me go ahead and just select something else so let's just one item here and click group by you'll notice that only one of the columns showed up I can just go ahead and click that plus sign and I can add another column so that was the group column so I'm going to go ahead and add that now what I want to do is I want to group by that item and group by that group and I want to do a count or a some of the quantity here so I'm going to go ahead just do a Qantas quantity just go and do quantity and what I want to do is I just want to sum those values here this value in this last column so if I do that now I want to select the column that I want to sum select on the quantity here I'll go ahead and click OK and it has grouped it so you see that the final output looks like this and this is in the query editor window what I can do is I can click close and load and it's going to open up a new worksheet here and bring that data in here so you can see that it is grouped by item and grouping and quantity now when we really think about it grouping should come first because item is going to be a subset of group what I can do here is I can go back let me go ahead and just I have my work queries here I'm going to go in click on I hover it over here it flies out this menu here I'm going to go and click edit another way to do is I can right click here and select edit and I'll bring me back into the query editor now what I can do here is I can move that grouping over I'm going to move this grouping over here just left-click drag and move it over so you can just think of this as the higher level parent of this of this child here so I'm grouping we have item we have quantity after I did that I can go ahead just click close and load is going to bring me back into the excel sheet and it's going to move that grouping back to the first column over back or move it to the first column now you may think okay I've got this grouping here I've got group item quantity why didn't I just make a pivot table so here's what here's an example of my pivot table here so if I select on this particular data table and go to insert and pivot table it's going to go through the process of letting me create a pivot table I'm just going to go ahead and create it within this particular worksheet location I'm just go ahead and select the l3 here click OK and I'm going to bring the group over here I'm going to bring the item over here and I'm going to bring the quantity over the value so it's going to sum it up since it knows it's numbers now this is in a compact format so usually I like to go ahead and go to design and change the report layout by default it is in compact form but if I change the tabular form you'll notice it looks almost very similar - what we have here in sheet 1 all I need to do is just adjust some of the subtotals and remove them what I can do is click within the pivot table and under grand totals I'm going to remove that off 4 rows and columns and remove the subtotals so that that's going to disappear now so click on that and now you notice this basically is almost the exact same is this where I actually have some of these headings here so let me go ahead and change that now I'm going to go ahead and go to design hit on the report layout I'm going to have repeat all items so group a is going to repeat it down here because group a item wanted to fall under group a so I'm going to click repeat all items you now to notice it looks very similar it's sorted by lowest to highest based on the sum of quantity if I did the same here and I sort it lowest to highest it'll look the same I think I'm gonna go back into the I'm go back and edit it back into the query editor and go under here and sort sort ascending 5 5 20 23 47 going to click close and load and now you notice it looks pretty much the same so I'm going to go ahead and show these two here you will notice that we have group a these 5 rows 5 5 20 23 47 and here we have 5 5 2023 47 so you might want to think to yourself why would I want to create a grouping with power query versus just doing it in Excel so let me go ahead and go back to the PowerPoint and show you why now if I showed earlier we have our power our pivot table that shows this outline and we have our power query which shows this I haven't created this extra column D here but with power query you can create this and it indicates that regions north and west are part of this row group a item two and this quantity of five so if we notice we have a group a which is here and item two which is there then we have our one there if we go down here group a item two we have also one here group a item 200 actually group a item two we have a three so that adds up to 5 so we have West and then we have West here and then we have North here so it tells us that the regions north and west comprised of that grouping we can't really get that from here unless we go and double-click this let me go back and look set and show you what that does so we're back in Excel and we showed item 2 here so if I double click the cell let me go and double click it it brings up another sheet and it shows you the items within that table so if I go here this particular grouping group a item to this quantity of 5 it brought back these 3 records let me go and double click it to autofit here it brought back these 3 records which fulfilled that row which are which comprise that row you can see if I sum it up it's 5 west west north now if we just wanted to have a roll that just showed us what regions are there in a comma separated values where we just have north and west we can create that in power query we can't really do that that easily in PowerPivot let me show you how it looks so I'm back here in Excel and I want to do is I want to edit that power query so I'm gonna go ahead and click here right click click Edit and I'm going to go ahead and change the groupings so so I want to change some of the parameters in how I group that data so I'm going to go in the third step here where it says group rows click this gear icon and it's going to bring back up the group by window here and what I want to do is I want to add an extra grouping here extra column so it's going to add an extra column here besides the quantity column go ahead and click the plus sign and what I want to do is I'm just going to call this a region list region I just didn't give it any name and I'm going to call it region list and instead of sum I'm gonna I want to call I want to have this all rows so it's going to bring up all the rows that comprise that particular row right if I click OK you'll notice that it put in a different column here and now if I just if I just click that particular column or let me go back to group a item to group a right if I click that particular cell in that column you see that it has given me that table and this is very similar to when I double-click the pivot table right we have these 3 records here and they add up to 5 it's north and to west so let me go back in the cell and you'll see what it looks like let me go ahead just close and load I'm going to change it later on it's brought this in here but let me show you what it looks it looks like so earlier you saw those three records right we have North let me go ahead and just make this in alphabetical order so we had north and we had the two west so at 131 so it's very similar to what we had there when we did the power query so let me go back into the power query window go to sheet 1 here and I'm going to right click edit or I just click here I can just hover over here and click Edit so maybe I want to create a separate column with comma separated values of what I showed earlier in the PowerPoint instead of having this table so if you click on this table let me go ahead and click on item 2 here so what this table does here this table context here under the under the column it just tells you that for that particular row there is a sub table or there's a table that comprises all the records in here so that table is available for us where we can do some further manipulation with that data so in this example what I showed here is we created another column that shows you comma separate value comma separated values of the regions that comprise that data in that particular row so what we need to do is kind of get it gets a little bit more sophisticated where we have to use the M code so the M code is basically the language that is used in power query to make these steps happen so for example this particular task it is this M code table dot sort and this arrangement of words so if I click on any each of these steps you'll notice that the arrangement or the syntax of commands is a little bit different so that's what actually M code looks like now when we click on the commands here basically that's creating M code for each of the steps if I go down each of these steps this is basically M code and if we want to create that comma separated value on this table and kind of manipulate some of the data here we have to write a little bit of M code or we originally got this idea from Ken pols and Miguel Escobar they have a fantastic website called power query training they've got a lot of good stuff there and I took one of the the snippets that they had and kind of adjusted it for this particular example so let me show you what that looks like so what I'm going to do is I'm going to add a column and put that end code into that column so I've got my my table here and I'm going to go under the add column tab click on add custom column and it's going to give me the window to add my column so I'm going to call this just the region list again and then the code is let me go and just paste the code here and I'll describe what it does so basically there are a couple commands within this particular set of M code so let me describe to you what the M code is doing here actually let me call this a new column name regionalist - because I named it one of the columns reading list the first one here so starting from the last or the most inner part of the M code syntax I have this table column expression or this function and what it's telling me is it's going to look at that region list that particular table I showed you at the bottom there and it's going to take that column out of there and once it takes that column out of there it's going to put it into this table dot from list expression and this from list is basically going to turn that table into a list and what this happens here after that it's going to take that output and take the distinct values from that list so as you know earlier from item two in grouping a we had a one North value and two West values and what does this think that this distinct table dot distinct expression does is it just takes the distinct values out of there so from that North comma West comma West is just going to take north and west and that's in a vertical list and what it's going to do here with this table adduct transpose is it's going to take that one column list and it's going to spread it over multiple columns basically one row and multiple columns so it's going to take one column for north and then one column for West and then it's going to take that value and put it into this table dot - list command and then join it so basically instead of having two columns it's going to combine those two columns together and combine them and then it's going to separate it by a comma here so let me click OK and show you the output so basically it looks like it didn't do really do anything but it created a separate column called regionalist too so if I clicked on this expander icon it's going to show me the particular output now so when I had my item 2 here we can't remember we had one north and two West in the table here let me go ahead and click on that you see you see that they had one north and 2's basically what it's done is it's put that into one column and then spread that over three columns so when you got to think about it where I had North West and West and now it took the distinct values it basically got rid of that one West and put that all into one cell here another example let me go ahead and go down to this particular row row four here where we had two Norse or three Norse in one West and basically it's made the distinct value in brought in one north and one West and put it all into one cell here so basically it's counting which particular regions make up this particular row here so if I click on go back to the home key if I click on close and load you'll notice that is brought back those four columns maybe I really didn't need this first column this column D let me go ahead and right-click and go it or edit and I'm just go ahead and get rid get rid of this click on this particular column right-click and click remove and we just have our four columns here click close and load and it's brought us back to the excel where now I have my grouping and I've got my distinct values for that particular record so as I showed you before we can do some extra manipulation using power query so basically what grouping does in power query it gives you a little bit more sophistication in terms of what you can do with your grouping you can do very similar grouping with a pivot table where we have our pivot table here and we have our quantity here but would power query you have a little bit more sophistication a little bit more manipulation that you can do with the data it's actually behind this particular row so if I double-click that we have our three records here for that particular row and with power query we can do some additional manipulation off of that record so that's just one example what you can do there's many other examples that you can do unfortunately you have to probably learn a little bit more on the EM query language but once you do it gives you a little bit more power in terms of what you can do with your groupings so I hope that helps thanks for watching you

Channel: Doug H

Views: 111,841

Keywords: rows, microsoft, learn excel, filter, grouping, m language, training, dough, doug, aggregate, msofficetrain101, csv, power query, excel 2013, tutorial, group, m code, pivot, tip, excel youtube channel, pivot table

Id: v72PR3o09P8

Length: 18min 24sec (1104 seconds)

Published: Sun Dec 04 2016