In the age of Big Data, the problem is no longer access to data, but understanding it. And with good reason: the sheer volume of data means that many organizations no longer know what to do with so much information, this is where Power Query aggregation comes into place.
Fortunately, there are several solutions to simplify the understanding and management of this data. One of these is Power Query aggregation. Find out what it’s all about, why it’s useful for businesses, and above all, what you need to do to aggregate data with this query editor.
What's the point of Power Query aggregation?
As a reminder, Power Query is a query editor for importing and modifying data, whatever its source or format. This is equally true for Excel and Power BI users.
Data aggregation in Power Query is just one of the many features of this query editor. It allows you to group and summarize data, making it easier to read and manage.
In doing so, you can obtain additional information, enrich your datasets, improve your reports, identify patterns, trends or anomalies, and also reduce the risk of errors and inaccuracies.
Aggregating data is particularly useful when dealing with large datasets. And yes, by summarizing massive raw data into smaller datasets, you can more easily analyze them and draw relevant conclusions.
How do I use Power Query aggregation?
Aggregate a column of data in Power Query
With Power Query, you can aggregate one or more columns from a linked table. For example, you could add to a price column, the sum of the unit prices for each order, the average of these unit prices, the minimum amount, the maximum amount, and so on.
To use Power Query aggregation with Excel, follow the steps below:
- In the “Home” tab, select “New source”, then “Other sources”, and the data source you need.
- A dialog box appears. Enter the URL of the table in which you wish to aggregate a column.
- Click on OK.
- In the “Browser” tab, select the table you wish to aggregate (you’ll be able to preview the data on the right).
- Click on “Transform data”.
- This brings up the Power Query editor. Scroll to the right until you see the column you wish to aggregate. Then click on the expansion icon to the right of the column.
- In the inset, select the “Aggregate” option, then an aggregation function item. Then choose one or more aggregation functions, such as sum, minimum, maximum, average…
- Click on OK.
Through these aggregation function elements, you can see the sum, minimum, maximum, average and so on.
Grouping data with Power Query
Through data grouping, you can view data at a higher (or aggregated) level.
Here’s how to do it:
- In the “Home” tab of the Power Query editor, click on “Group”.
- A window appears where you can select one or more variables by which you wish to group your data.
- If you wish to group data by several variables, or perform an operation on several variables, choose the “Advanced” option. Otherwise, the “Basic” option is sufficient.
- Choose from several options for your data output:
- Name your aggregated column on Power Query ;
- Choose an aggregation function (sum, average, minimum, maximum…);
- Select the column and click OK.
- Then click on “Close and Download” to move the data to the spreadsheet.
Through these aggregation function elements, you can see the sum, minimum, maximum, average and so on.
💡Related articles:
Search for data associated with a column
In addition to aggregating data, you can also display the data source. This will show you where the data comes from.
Here’s how to do it:
- In the “Query parameters” tab, select “Source” (which appears under “Applied steps”), then click on the parameters icon.
- The “Source” dialog box appears. Make a note of the data source name, as you’ll need it later.
Return to the “Home” tab, then click on “Sources”. At this point, select the data source you noted earlier. - The “Navigator” dialog box appears, listing all the tables in the data source. Select only the associated table of interest. In the preview, you can also display only the relevant columns using the display options.
Would you like to master Power Query and its various queries? The only solution is training. DataScientest offers training courses that combine theory and practice, so you’ll be up and running by the end of the course.
Things to remember :
- Power Query’s aggregation option lets you group and summarize data.
- This feature is particularly useful for data experts dealing with large volumes of data.
- Depending on your needs, you can display sums, averages, minimums, maximums… of numerical data linked between several tables.
