How to Clean Data in Pivot Query in Excel

For instance, I am using Adventure work. But data need to be in Excel format.

Cleaning First Dataset from the Folder

Step 1. From the Ribbon tab click on "Choose Column" Two options will appear. Click on "Choose Column" and select the columns you require. (eg., Product key, order datekey, ship datekey, due datekey, customer key, sales territory key, order quantity, unit price, product standard cost).

Step 2. Double-click on Heading to change any column name. (eg., Product standard cost -> cost)

Step 3. Select the whole table by Ctrl + A and go to Transform -> Detect Datatype. ( By this method we can detect the datatype of the table in one go)

Step 4. Click Add Column -> Custom Column and create your required column. We need to create a total Revenue Column, and for this, we need to create a formula.

Example

• TOTAL REVENUE = ORDER QUANTITY * UNIT PRICE
• COST OF GOODS SOLD = ORDER QUANTITY * COST
• TOTAL PROFIT = TOTAL REVENUE - COST OF GOODS SOLD

Cleaning Second Dataset from the Folder

Step 1. Remove all the columns from the data that are not required.

• For Row: Select the filter from the heading and remove the options not required. (eg., 0 / NA / etc)
• For Column: Right-click on the heading and select Remove. OR Right-click on the heading and choose "remove another column" Then we can work on one specific column and the rest will be deleted.

Cleaning Third Dataset from the Folder

Step 1. Select the columns that are not required. (eg., Product key, English Product Name, Color)

Step 2. From the column to Replace the NA values. Right-click on the heading and click "Replace Values" (Replace Values are case sensitive)

Cleaning Forth Dataset from the Folder

Step 1. Select the column that is required Home -> Choose Column-> Choose Column. (eg., City, Sales Territory Key, English Country Region Name)

Cleaning Fifth Dataset from the Folder

Step 1. If we require only one column, right-click on that column heading and click on remove every other column. (eg., Full Data Alternate Key)

Step 2. Click on the particular column and click on Add Column -> Date -> Year/Month/Day/Date. (If we work on the Date of Transform tab then it will overwrite and not create a new column)

Step 3. If we feel month names are long. Click on Transform -> Extract (a dialogue will appear) -> First Character -> Write no. of letters that need to be extracted. (eg., January -> Jan)

Step 4. For applying any condition - Click on Add Column -> Condition Condition -> Type the column name, condition, values, output, and else statement. (eg., Column name - Week type, Condition - day names, equals, values - Sunday, output - Weekend and else - Weelday) Multiple columns can be added for the condition in one other condition.

Step 5. If anything needs to be added before the value, Transform -> Format -> Add prefix -> Type prefix or special character.

Cleaning Sixth Dataset from the Folder

Step 1. We need to merge two columns so need to select first column and the select the second column by Ctrl + Click. Click on Transform -> Merge columns. (eg., First column - First name, Second column - Last name, Merged column - Full Name)

Step 2. Select the columns required Home -> Choose Column-> Choose Column. (eg., Customer key, Geography key, Customer Alternate key, Full Name, Gender)