Reshaping and aggregating tables in Power Query
1. Reshaping and aggregating tables in Power Query
Reshaping data is an important step in preparing it for analysis in Power BI. Even if your data is clean and of high quality, you will often need to restructure it so that it is more suitable for analysis. Luckily, there are many features in power query that can help you accomplish this. Let’s first look at this data here. We can see that we have 5 columns and 4 rows in this data set. The orientation of this table is such that the data is organized in a row-wise format, whereas in Power BI, you always want your variables to be organized into columns. Which means we need to switch the rows with the columns. This can be achieved by using the transpose feature. But first, let us demote the column headers to become the first row of data. After demoting the headers, we will use the transpose feature in the transform menu. Then we will promote the headers to reverse our previous demotion step. This results in a table that is much more suitable for building measures and analyzing since each column is represented by a variable. We can also rename the first column to “Product” so that it is more descriptive of its contents. The next transformation we’ll learn is also great for reshaping data that is organized into rows but in a different way. Here, we have a dataset that will look quite familiar to you if you have done any sort of financial planning or budgeting in Excel before. The first column represents the business categorization, while the second column divides the data into rows of revenue, cost, and profit. Subsequent columns contain data for each month in separate columns. While this format is very handy for use in Excel, we cannot really use it in Power BI. Once again, we will transform this from a row-wise format to a column-wise format. This is where we can use the unpivot feature. But first, let’s use the fill feature to fill in the missing values in the first column. Then we’ll select the first two columns of data, right-click, then select “Unpivot other columns”. This will transform our data from a wide shape to a long shape. It is important to note that the data itself was not changed at all, only restructured. It can still benefit from additional restructuring though, as the value column here contains the information for Revenue, Cost, and Profit, all in one column, which is not very practical for building measures. Ideally, we would have a separate column for Revenue, Cost, and Profit. This is exactly what the pivot transformation does, so let’s use it to create new columns in our dataset. Similar to excel pivot tables, you can choose an aggregation to apply to your values, but for now, we will just stick with Sum. This puts our data in the following format. If we’re interested in looking at the totals for each business category, we would need to sum up the values in each column that correspond to the correct row in the Category column. This is where the group by function comes in. We group our data using the “Category” column, then add three aggregations, one for each column we are interested in. Alternatively, we could group the data by Month instead, while using the same aggregations as before, simply by changing the grouping in the group by step. Now you’ll use these features to reshape sales data for Northwind traders, a specialty food & beverage supplier in North America.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.