---
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)