Power BI Exercise – Data Transformation in Power Query

Enhance your Data Transformation skills in Power Query. Learn how to split dates into Year, Month, and Day columns, remove duplicate rows based on OrderID, and calculate a 10% tax on sales amounts using custom columns.

Tasks:

1. Split the Order Date column into separate Year, Month, and Day columns.
2. Remove duplicate rows based on OrderID.
3. Add a new column that calculates a 10% tax on the Amount.

Solution:

Load the below dataset into Power BI-

Orders Dataset

Split the Order Date column into separate Year, Month, and Day columns-

Step-1: Right-click on the Orders dataset and select Edit Query.

Step-2: The Power Query Editor window will open. Then, select the Order Date column and click on Split Column > By Delimiter.

Step-3: Select the options below. Here, we will split the column using the hyphen (-) as the delimiter.

Step-4: As shown in the screenshot below, the Order Date column has been split into three separate columns: Day, Month, and Year

Step-5: Now, double-click on the column name to rename it.

Remove duplicate rows based on OrderID-

Use the Remove Duplicates feature to eliminate duplicate rows by selecting the OrderID column.

Right-click on the OrderID column and select Remove Duplicates. This will remove the duplicate OrderID rows, if any, and a new transformation step will be added under Applied Steps.

Add a new column that calculates a 10% tax on the Amount-

Add a Custom Column with this formula. Refer to the screenshot below

[Tax amount] = [Amount] * 0.1

• Click on the Add Column tab.
• Next, click on Custom Column.
• Provide the column name as Tax amount.
• Select the Amount column, click on << Insert, and then add * 0.1 after [Amount].
• Finally, click the OK button

See the result of the Tax Amount in the screenshot below.

Next, go to the Home tab, click on the Close & Apply dropdown, and select Close & Apply to save the transformation changes.

I hope you enjoyed this Power BI exercise.