Power Query is a very strong tool available in Excel. It allows you to perform different types of transformations on your data. One of the features offered by this tool is that it allows you to group different rows together in order to get the count of the entire group in a very easy and convenient fashion. In order to walk you through how to do that, an overview of the different steps involved is discussed below.
Step 1: In the first step, you need to select all the data you want to process and convert it into a table form. You can do that by clicking on ‘Insert’ followed by ‘Table’. Or you could simply press Ctrl + T for this purpose.
Step 2: In the second step, you need to click on ‘Data’ followed by ‘Get and Transform’. Then click on ‘From Table’. You can also do that by clicking on ‘Power Query’ followed by ‘Excel Data’ and then by clicking on ‘From Table’. Power Query Editor will be opened now.
Step 3: If you want to group your data with respect to Country in order to show the number of times each country is present within the data, you need to go to ‘Transform’ and click on ‘Group by’.
Step 4: You need to select ‘Country’ while you are clicking on ‘Group By’. Then you need to click on ‘Count Rows’ for this operation. Now your table would group up all the values with respect to country and would also show you to counts of each country within the data. For instance, USA appears 7 times in the data.
Step 5: When you are done with the 4th step, you would see that the changes have now taken place and the data is properly grouped together.
Step 6: In the final step, you need to go to ‘Click and Load’ by clicking on the ‘Home’ tab. This will result in opening up a new worksheet comprising of new data.