Using Group-by in Power BI for Data Transformation
Data Transformation is a process in which raw data is first transformed into a format that is suitable for fast and precise data processing, as well as for efficient reporting. Moreover, data transformation involves a wide range of operations, such as identifying data sources and data types. Next, it includes cleaning data by removing errors and duplicates, in addition to enriching data and performing aggregations.
Data Transformation Principles:
The basic data transformation principles, worked out by our experts after delivering multiple Power BI training sessions, are listed below:
Start by selecting queries via Navigator.
Then, remove blank rows from the top of sheets.
Use the first row as a header.
Next, remove blank columns.
After that, choose relevant columns.
Rename column names for clarity.
Change data types as required.
Remove any errors in the data.
Clean and trim the text values.
Correct any spelling mistakes.
Filter data based on specific conditions.
Remove duplicate entries.
Afterwards, explore and edit the applied steps.
Check for repeatable workflows.
Finally, work with the Advanced Editor for deeper customisation.
In this blog post, we will walk through the use of Group-by function at basic and advanced levels. To understand the concept and follow through, you must familiarize yourself with the data used in this tutorial. A screen shot of sample data is attached below:
Data Transformation is performed inside the query editor:
To open the Query Editor:
- Click on the Home tab in the Ribbon Menu.
- In the External Data section, click on Edit Queries.
- Now click on Edit Queries to enter the Query Editor.
A new window opens showing the interface for Microsoft Power BI Query Editor.
Using Basic “Group by” Functionality
“Group by” functionality is used to calculate Aggregated Values for a given field.
1.Click on Sales in the Queries Section.
2.Click on the header of the column Order Method Type.
3.In the Ribbon Menu, on Transform.
4.Click on Group by in the Table Section.
A pop-up menu appears.
5.Under the Group by option, choose order method type from the drop-down
6.Under the New Column Name option, type “Total Sales”.
7.Under the Operation option, select sum from the drop-down menu.
8.Under the Column option, select Price from the drop-down menu.
Click OK.
An aggregated view of the table appears.
As shown above, Web is the highest contributor in the total sales.
Using Advanced Group by Functionality
Advanced Group By functionality allows you to calculate aggregated values for more than one field. Let’s see how this works. Let’s see how this works.
In the Query Editor, undo the last step of “Grouped Rows” for Sales Table.
To use the Advanced Group By functionality:
- Click on Sales in the Queries
- Click on the header of the column order method type.
- From the ribbon click on
- Click on Group by in the Table Section.
A pop-up menu appears
- Select the Advanced by clicking to the circle at the left of Advanced checkbox.
- Under the Group By option, choose order method type from the drop-down
- Click on Add grouping.
- Select Urgent? from the drop-down menu in this field.
- Under the New Column name option, type Total Sales.
- Under the Operation option, select sum from the drop-down menu.
- Under the Column option, select Price from the drop-down menu.
Click OK.
An aggregated sales table based on Order Method Type and Urgent Field appears.
13. Click on the drop-down next to the header Total Sales type and click on Sort Ascending.
It is found that Web contributed to the highest sales, when the Urgent option was chosen as YES.
Conclusion:
Data Transformation is one of the core concepts of Data Analytics and data reporting. Data transformation refers to the series of steps performed on the raw data to make it suitable for analysis. In this blog post, the data transformation using the “group-by” feature in Power BI query editor has been discussed.
Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses: