Blog

In this documentation, we will know how to perform basic data transformation in PowerBI on data that usually need in data cleansing. We will focus on some following transformations:

Dataset Description

This sales dataset captures product sales details, including dates, customer information, product pricing, quantity sold, taxes, and profits, enabling analysis of revenue, customer behavior, and location-based sales trends. The link to the dataset is here

Date	Product selling date
Product	Name of the product
Quantity	No. of product sold
Price	Actual price of a product
Selling Price	Selling price of a product
Tax (%)	Added tax on total price of product
Total Price	Total price of the product including tax
Total Profit	Total profit got on the products
Customer	Name of the Customer
Location	Location of the customer

Let’s perform each transformations one-by-one:

Data Type Conversion

In this transformation, we convert data type of column/feature(s) to correct data type.

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Select Total Price column and select Data Types: Whole Number
   

1. Change Column Type pop up window will be open, select Replace current.
   

Rename Column

In this transformation, we change heading of the column/name of the column to make sure to look good.

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Select Tax (%) column and select rename in Any Column under Transform tab
   

1. Type GST (%) and hit Enter button.
   

Remove Column(s):

In this transformation, we can remove one or multiple column that not useful for the insight.

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Select Customer column, click on Remove Columns, and select Remove Columns.
   

Duplicate Column

In this transformation, we can create a duplicate column of selected column.

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Select Location column, click on Duplicate Column from Add Column.
   

Move Column

In this transformation, we can move the place of column in a dataset.

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Select Date column
2. Go to Transform tab; click Move, and select To End.
   

Remove Rows:

In this transformation, we can remove selected or in range rows.

Remove Top Rows:

In this transformation, we can remove/delete top N rows.

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Click on Remove Rows and select Remove Top Rows from Home tab.
   

1. A pop up will be display to enter no. of rows to delete.
   

1. Hit OK.
   

Remove Bottom Rows:

In this transformation, we can remove/delete bottom N rows.

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Click on Remove Rows and select Remove Bottom Rows from Home tab.
   

1. A pop up will be display to enter no. of rows to delete.
   

1. Hit Enter.
   

Remove Duplicates

In this transformation, we can remove/delete duplicate rows according to selected column.

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Select Date column
2. Go to Home tab and click on Remove Rows to select Remove Duplicates.
   

Remove Blank Rows:

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Select Date column
2. Go to Home tab and click on Remove Rows to select Remove Blank Rows.
   

Keep Rows

Keep Top Rows:

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Go to Home tab and click on Keep Rows to select Keep Top Rows.
   

1. Now type the no. of row you want to keep from top and hit OK.
   

Keep Bottom Rows:

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Go to Home tab and click on Keep Rows to select Keep Bottom Rows.
   

1. Now type the no. of row you want to keep from bottom and hit OK.
   

Keep Range of Rows:

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Go to Home tab and click on Keep Rows to select Keep Range of Rows.
   

1. Now type first row and the no. of row you want to keep and hit OK.
   

Keep Duplicates:

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Select Customer
2. Go to Home tab and click on Keep Rows to select Keep Duplicates
   

Filter Rows:

Procedure to perform:

Step

1. Open Microsoft Excel or Power BI Desktop
   

1. In Excel, click on New Query under Data tab whether in Power BI Desktop, click on Get Data under Home tab.
   

1. Choose from File>From CSV in Excel whether choose Get Data>Text/CSV in Power BI Desktop.
   

1. Navigate the CSV file and select to open.
2. A preview window will load, select Edit in Excel whether select Transform Data in Power BI Desktop
   

1. Select Location column and click on dropdown icon in Location
   

1. Now unselect (Select All) and select Delhi, hit OK
   

Conclusion

With the help of above transformations, we have gotten the ideas about how the data can be clean for further process like finding insights, visualization etc.

If you like this blog, you can share it with your friends or colleague. You can connect with me on social media profiles like LinkedIn, Twitter, and Instagram. If you have any doubt feel free to comment.

• 👏 Like for this article and subscribe to our newsletter
• 📰 View more content on my DataSpoof website
• 🔔 Follow Me: LinkedIn| Youtube | Instagram | Twitter