120Reshaping Your Data
6.You can see that now we have a defined schema with correct column headers:
Figure 4.20 – Cleaned data
When promoting rows to column headers, you will see that a Changed Type1 step is applied automatically.
Grouping data
We connect to a wide variety of data and usually connect to data with high levels of detail that it may not need for reporting. Instead of loading all data, we can define relevant aggregations and group data according to custom logic at the Power Query level. In this recipe, we will see how to define grouping logic and how to aggregate data easily.
Getting ready
For this recipe, you need to have Power BI Desktop running on your machine. You need to download the following file in a local folder:
• FactInternetSales CSV file
In this example, we will refer to the C:\Data folder.
Grouping data 121
How to do it
Once you open your Power BI Desktop application, you are ready to perform the following steps:
1.Click on Get Data and select the Text/CSV connector.
2.Browse to your local folder where you downloaded the FactInternetSales CSV file and open it. A window with a preview of the data will pop up. Click on
Transform Data.
3.Browse to the Transform tab and click on Group By:
Figure 4.21 – Group By
4.Flag Basic and select the ProductKey column. In New column name, type
SalesAmount, define Sum as Operation and SalesAmount as the Column on which to perform the sum, and click OK:
Figure 4.22 – Group By Basic
122Reshaping Your Data
5.You can observe how the aggregation was performed by summing SalesAmount for each ProductKey:
Figure 4.23 – Group By output
This is a simple aggregation based on one column, but this feature allows you to apply advanced grouping logic, as shown in the following steps:
1.Double-click on the Grouped Rows step in order to open the Group By window and edit the step we defined previously:
Figure 4.24 – Grouped Rows step
2. Flag Advanced and enter the information as seen in the following screenshot:
Grouping data 123
Figure 4.25 – Group By Advanced
3.You can see that we defined an advanced grouping logic that aggregates data by
ProductKey and TerritoryKey.
You have many possibilities on how to aggregate data. You can perform different built-in calculations, for example:
Figure 4.26 – Group By calculations