Power Query – Grouping Option

In this blog-Post, we will discuss the feature “Group By” from the Microsoft Power Query Add-In.

When we need to get the summary of any database in excel. we have been using a Pivot table, SumIF, SumIFS from a long time in excel for the same. we all are very well versed with Pivot Table, However, when we have a heavy database, then most of the time, when we convert our data into the pivot table. Excel starts buffering and gives us a message “Excel is not responding”. Does it not happen?

Today we are going to learn another strong way to prepare a summary report in excel. without any interruption, no matter how big data we have. without using any formula or function and data will be refresh automatically when more data will be added into the table. It sounds a bit awkward to hear but it’s possible.

Excited! Han let’s break this mystery and understand the procedure.

Note: As we are learning about the Microsoft Power Query. We need to download this add-in if we are using Ms-office 2013 version. If any user is using below of Ms-Office 2013 version, it cannot use it. We have already explained in detail, the installation procedure in our previous blog-post. Click on the Blog-post columns on the website and read the complete blog-post.

Let’s take an example-

Refer to the above screenshot, we have the product and their sales representative wise sales report for the month of February. By the end of the month, we want a summary report based on sales representative wise sales report (refer to the summary report in column F: G).

Step:1-

Convert the database into the Table format-

1. Select the database
2. Insert Tab>> click on Table ->> Checkmark on “My table has the header”. If already clicked then leave it->> Ok
3. We can use the shortcut key (Ctrl+T) to convert the database into table format

Step:2

1. Go to Data Tab
2. Click on Select Table/Range

Step:3

When we click on this option “From Table/Range”. It takes us on the new window “Power Query Editor”. Refer the below mention screenshot-

1. Query Editor Page- The above screenshot is called “Power Query Editor”
2. Ribbon Tab- The ribbon tab is the combination of different command from the different group. For example, File, Transform, Add column, etc.
3. We use the natural language in the form of M-code
4. Database which we imported into the power query
5. Query setting & Applied Steps- Here we can define the name of this query. We can analyze the applied steps. We can edit, and we can delete the steps. The undo (Ctrl+Z) option is not working into the power query.

Step4:

1. Select the column SalesRep
2. Either click on Home or Transform
3. Click on Group By

Step:5

When we click on option Group by. We will get a new dialogue box. In which there are two option available-

1. Basic
2. Advance

Let’s discuss one by one –

Basic: when we want to prepare a summary report which is based on one condition. For example, If we want to prepare a summary report based on Product-wise Total sales-

1. Group By- Choose the heading “Product” from the drop-down list
2. New Column Name- Define any name for the heading purpose. Example- “Total Sales”
3. Operation when we click on the drop-down button located inside the operation dialogue box. The following list will be displayed, in which we can choose any one of them. For this question, we will choose the function “SUM”-

• Sum
• Average
• Median
• Min
• Max
• Count Rows
• Distinct count
• All Rows

4. Column: In the column dialogue box, choose the heading which we want to get total. For example, Sales. Refer the screenshot below-

5. Ok

The following result will be displayed when we click Ok-

Advance: when we have multiple conditions to prepare a summary, the report then would use this option. For example, if we want to prepare a summary, report which is based on Sales re-presentative and product-wise total sales. Let’s see how we can do that-

1. Select both columns with holding Shift+click
2. Open Group By

3. Select Advanced

4. Group By- Choose the first heading from the drop-down list

5. Add Grouping- By clicking on this option, we can add more group, for example, click and select “Product” from the drop-down list

6. New column Name- Type any name for example “Total sales”

7. Operation- we want to get the total sales. so will choose “Sum” from the drop-down list

8. Column- To get the total sales, we will select the heading “Sales”

When we click on Ok, the following result will be displayed-

Not only we can add the multiple groups but also, we can add multiple calculation columns. For example, If we want the summary, of Total sales, Total count, Total average, Maximum and minimum sales of Sales re-presentative with the product.

Click on the option “Add aggregation” and Add new calculative column. Refer the screenshot below-

When we click on Ok, we will get the following result-

When our summary report is ready, Now it’s time to export into the excel.

1. Go to Home/File
2. There will be two option-

• Close & Load- If we click on this option, the result will be load on the new sheet
• Close & Load to- If we choose this option, it asks us “How we want to view this data into this workbook”.

We want this data should be import on the same workbook. So, we will choose the second option- “Close & Load to”. The following option will display in below mention screenshot –

We will choose the option existing worksheet and define the cell address F3 for exporting data here.

And click OK, Data will be exported into your workbook. Refer the screenshot below-

Follow these steps to prepare the summary report, no matter how big the database we have.

Hope this blog-post helps to create a summary report. Do the practice and let us know if any query occurs. We would feel great to assist you.

Stay tuned and connected with Nurture Tech Academy.

Happy Learning 😊