Data Transformation is a process in which raw data is Transformed into a format that is suitable for fast and precise data processing and for efficient reporting. Data Transformation involves a wide range of operations such as identifying data sources and data types, cleaning data by removing errors and duplicates along with 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:
- Select Queries via Navigator.
- Remove blank rows from the top of sheets.
- Use first row as a header.
- Remove blank columns.
- Choose relevant columns.
- Rename column names.
- Change data types.
- Remove errors.
- Clean and trim data.
- Correct spelling mistakes.
- Filter data based on condition.
- Remove duplicates.
- Explore and edit applied steps.
- Check repeatable workflows
- Work with advanced editor
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 a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide our services in the following regions
