---
# System prepended metadata

title: Day 3 - Transform Data
tags: [pbionline]

---

---
title: Day 3 - Transform Data
tags: pbionline
---

## Change Data Source Setting
Change data source of two queries:
- ResellerSalesTargets
- ColorFormats

## Configure the Saleperson query
- Rename query DimEmployee -> Salesperson
- Filter the query rows to get only employees who are salespeople (SalesPersonFlag -> select TRUE)
- Only keep the necessary columns: 
	- EmployeeKey
	- EmployeeNationalIDAlternateKey
	- FirstName
	- LastName
	- Title
- Merge column: FirstName and LastName, then change to Name
- Change column name: EmployeeNationalIDAlternateKey -> EmployeeID

Verify the query: 4 columns, 18 rows

## Configure the SalespersonRegion query
- Rename query: DimEmployeeSalesTerritory -> SalespersonRegion
- Remove the last two columns: DimEmployee and DimSalesTerritory

Verify the query: 2 columns and 39 rows

## Configure the Product query
- Rename query DimProduct -> Product
- Filter FinishedGoodsFlag, choose TRUE (only contains finished products)
- Keep the following columns:
	- ProductKey
	- EnglishProductName
	- StandardCost
	- Color
	- DimproductSubcategory

- Expand the DimproductSubcategory, choose: **EnglishProductSubcategoryName** and **DimProductCategory**
(remember uncheck the Use Original Column Name as Prefix)
- Expand the DimProductCategory, choose EnglishProductCategoryName
- Rename columns:
	- **EnglishProductName** to **Product**
	* **StandardCost** to **Standard Cost** (include a space)
	* **EnglishProductSubcategoryName** to **Subcategory**
	* **EnglishProductCategoryName** to **Category**

Verify the query: 6 columns and 397 rows

## Configure the Reseller query
- Rename query DimReseller -> Reseller 
- Keep the following columns:
	- ResellerKey
	* BusinessType
	* ResellerName
	* DimGeography
- Expand DimGeography, choose the following columns:
	- City
	- StateProvinceName
	- EnglishtCountryRegionName
- Correct value in BusinessType columns: Ware House -> Warehouse
- Rename columns:
	* **BusinessType** to **Business Type** (include a space)
	* **ResellerName** to **Reseller**
	* **StateProvinceName** to **State-Province**
	* **EnglishCountryRegionName** to **Country-Region**

Verify the query: 6 columns and 701 rows

## Configure the Region query
- Rename query DimSalesTerritory -> Region
- Remove value 0 of column SalesTerritoryAlternateKey
- Keep the following columns:
	- SalesTerritoryKey
	* SalesTerritoryRegion
	* SalesTerritoryCountry
	* SalesTerritoryGroup
- Rename columns:
	* **SalesTerritoryRegion** to **Region**
	* **SalesTerritoryCountry** to **Country**
	* **SalesTerritoryGroup** to **Group**

Verify the query: 4 columns and 10 rows

## Configure the Sales query
- Rename query FactResellerSales -> Sales
- Keep the following columns:
	- SalesOrderNumber
	* OrderDate
	* ProductKey
	* ResellerKey
	* EmployeeKey
	* SalesTerritoryKey
	* OrderQuantity
	* UnitPrice
	* TotalProductCost
	* SalesAmount
	* DimProduct
(a small percentage of **FactResellerSales** rows had missing **TotalProductCost** values. The **DimProduct** column has been included to retrieve the product standard cost, to fix the missing values.)
- Expand DimProduct, include StandardCost column

- Create a custom column names Cost, with the following syntax:
```
if [TotalProductCost] = null then [OrderQuantity] * [StandardCost] else [TotalProductCost]
```

- Remove columns: TotalProductCost, StandardCost
- Rename columns:
	- **OrderQuantity** to **Quantity**
	* **UnitPrice** to **Unit Price** (include a space)
	* **SalesAmount** to **Sales**
- Change data type of Quantity column: Whole Number
- Modify data type to Fixed Decimal Number
	- Unit Price
	- Sales
	- Cost

Verify the query: 10 columns and 999+ rows

## Configure the Targets query
- Rename query ResellerSalesTarget -> Targets
- Unpivot columns: M01-M12, column names appear in the Attribute columns and values appear in the Value column
- Filter Value column to remove hyphen (-) values
- Rename columns:
	- Attribute -> MonthNumber
	- Value -> Target
- Create a column from example: Create date column from Year and MonthNumber column. Then change its name to TargetMonth (eg. Month/Day/Year, create from 2-3 examples).
- Remove columns: Year, MonthNumber
- Modify date types:
	- Target -> fixed decimal number
	- TargetMonth -> date
- Multiply the Target values by 1000 (Number Column group -> Standard -> Multiply)

Verify the query: 3 columns and 809 rows

## Configure the ColorFormats query
- Promote first row as header

Verify the query: 3 columns and 10 rows

## Update the Product query
By merging the ColorFormats query

- Combine -> Merge Queries, at Color column
- Expand ColorFormats to include columns: Background Color Format, Font Color Format

Verify the query: 8 columns and 397 rows

## Update the ColorFormats query
Uncheck the Enable Load To Report checkbox on Properties of ColorFormats query.

## Finish Up
Eight queries with correctly names:
	* Salesperson
	* SalespersonRegion
	* Product
	* Reseller
	* Region
	* Sales
	* Targets
	* _ColorFormats_ (which won’t load to the data model)