Power Query: Expanding on Aggregating
8 November 2017
Welcome to our Power Query blog. This week I look at bringing in two tables of data and summarising some of that data. At the same time.
I have two tables of data which give me information about my items and charges, as shown below. I have several goals: I need report on the key item data and work out the total and average sales for each item.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image1.png/e7b367af3e65df695b35f08904d278d8.jpg)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image2.png/cb0164cd88d8718ea561c6ea395d003f.jpg)
I am going to create two queries, one to extract data from my Access database for each of my tables. As I am now using Excel 2016, I use the ‘New Query’ dropdown from the ‘Get and Transform’ section on the ‘Data’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image3.png/209bfc65fb782bbc56f788d2d5cf17fe.jpg)
I choose to upload my data from the ‘Microsoft Access Database’ option, and having selected my database, the tables available are shown below.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image4.png/c7a7c0c8e1ddd9e8d2666ac1e5586602.jpg)
Since I am getting both my tables from this database, I check the ‘Select multiple items’ box.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image5.png/6d1417b58eb81fe007bf51dad1076824.jpg)
Checkboxes appear next to my tables, and I can select both tables that I am interested in: ‘ACCT_Order_Charges’ and ‘Items’. I can create both queries at once! I choose to ‘Edit’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image6.png/e4d5aaf7838c21fd80ca556f562e4c18.jpg)
My two queries have been created. I will merge them, using the option ‘Merge Queries’ in the ‘Combine’ section on the ‘Home’ tab in the editor.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image7.png/42b4000ca6844adeeae1b0268f285b66.jpg)
I choose to create a new query with my merged data, by choosing the ‘Merge Queries as New’ option.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image8.png/c3a793b9c2a6bfdba882df3173f3cef1.jpg)
I need to select the field Item_Key from both queries, as this is how my queries are linked. Not all my charges data is related to items – some are delivery charges, so not all the charges will be merged. I choose the ‘Left Outer’ join as I want all my items, and only the charge data that relates to my items:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image9.png/09322c92a35d761414227253df41b665.jpg)
A new query, ‘Merge1’ has been created.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image10.png/fb74bfc421b3ac368389bfeb1154bf5d.jpg)
Next to my item data, there is a table column, which holds the data from the charges query under the heading ‘ACCT_Order_Charges’. I can click on the icon next to the ‘ACCT_Order_Charges’ heading and expand my data – but that’s not all I can do.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image11.png/ce98544e6c57c26803e7cd9c01e10743.jpg)
I want to find out the total and average sales for each item, so I can choose to ‘Aggregate’, rather than ‘Expand’. I could expand and then group my data if I needed more information from ‘ACCT_Order_Charges’, but in this case, I am only interested in the ‘Amount’. When I select the ‘Aggregate’ option I can see a number of calculations including ‘Sum of Amount’. I also choose not to have a prefix. However, there are more options next to the ‘Sum of Amount’, as shown below:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image12.png/479b45c16df8c6701b1ababede2a401a.jpg)
I can also see other calculations associated with the amount, so I choose the ‘Average’ too. This selection changes the ‘Sum of Amount’ title on the Expand / Aggregate screen.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image13.png/ba66c88c664855800e1d0f3a3656e51b.jpg)
I choose ‘OK’ (I can click anywhere on the Expand / Aggregate screen to hide the ‘Aggregates of Amount’ dropdown) to see my results. I have deleted some of the irrelevant columns below to make the results easier to view:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image14.png/e82a6ecea3eda35747701b6e77ee4af5.jpg)
All I need to do now is make my headings and query name more useful, and I have my results ready to load to Excel, using the ‘Close & Load’ option.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-49-image15.png/1d6d67dfcb3c7d9d78fe144c788dde80.jpg)
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!