Blog

You are currently viewing Basic Data transformation in PowerBI

Basic Data transformation in PowerBI

Loading

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
3001.PNG

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

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

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
3001.PNG

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

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

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
3001.PNG

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

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
3001.PNG

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

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
3001.PNG

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

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
3001.PNG

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

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

  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
3001.PNG

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

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

  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
3001.PNG

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

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
3001.PNG

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

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
3001.PNG

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

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

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
3001.PNG

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

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

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
3001.PNG

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

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

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
3001.PNG

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

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
3001.PNG

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

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

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.

Leave a Reply