Power Query – Data Transformation

Module 3: Data Transformation Techniques

Objective

Clean and reshape data using Power Query’s built-in tools in Excel.

Files Used

• Download Survey_Results.xlsx
• Download Orders_2023.xlsx

Removing Blank Rows and Unnecessary Columns

Steps:

1. Go to Excel → Data tab → Get Data → From File → From Workbook
2. Select and load Survey_Results.xlsx
3. In Navigator → select the worksheet → click Transform Data
4. In Power Query Editor → Go to Home tab → click Remove Rows → choose Remove Blank Rows
5. To remove unwanted columns:
   • Click the header of the column you want to remove
   • Hold Ctrl and select additional columns if needed
   • Right-click any selected header → choose Remove Columns

Changing Data Types

Steps:

1. In Power Query Editor, locate the column you want to change
2. In the column header, click the small data type icon (e.g. ABC123 or calendar icon)
3. Select the appropriate data type: Text, Whole Number, Decimal Number, Date, etc.
4. Alternatively, go to the Transform tab → click Data Type dropdown → choose desired type

Renaming Columns

Steps:

1. In Power Query Editor, right-click on the column header you want to rename
2. Click Rename
3. Type the new column name and press Enter
4. OR: double-click on the column name to rename directly

Filtering and Sorting Data

Steps:

1. In Power Query Editor, locate the column by which you want to filter or sort
2. Click the dropdown arrow in the column header
3. To filter:
   • Check/uncheck boxes or use number/text filters
4. To sort:
   • Click Sort Ascending or Sort Descending in the same dropdown menu

Grouping Data

Steps:

1. Select the column that contains the values to group (e.g. Region or Category)
2. Go to the Transform tab
3. Click Group By
4. In the dialog:
   • Choose Basic or Advanced
   • Select the column to group
   • Choose operation (e.g. Count Rows, Sum, Average)
5. Click OK

Splitting Columns

Steps:

1. Select a column (e.g. Full Name or "City, State")
2. Go to the Home tab → click Split Column
3. Choose a method (e.g. By Delimiter)
4. In the dialog:
   • Choose the delimiter (e.g. comma, space, custom)
   • Choose how to split (e.g. at each occurrence, left-most, right-most)
5. Click OK

Unpivoting Columns

Goal: Reshape data so that monthly sales are listed in rows rather than across columns.

File used:

• Download Monthly_Sales_2023.xlsx

Steps:

1. In Excel → go to the Data tab → click Get Data → From File → From Workbook
2. Select the file: Monthly_Sales_2023.xlsx and click Import
3. In the Navigator window:
   • Select the worksheet that appears (there is only one sheet in this file)
   • Click the button Transform Data to open Power Query Editor
4. In Power Query Editor:
   • Confirm that columns like `Jan`, `Feb`, ..., `Jun` are present
   • Select all month columns by clicking on `Jan`, then holding Shift and clicking `Jun`
5. Go to the Transform tab → click Unpivot Columns
6. Power Query will convert each of the selected columns into two columns:
   • One called Attribute (containing the month names)
   • One called Value (containing the corresponding sales values)
7. You can rename the new columns:
   • Right-click the column Attribute → choose Rename → type `Month`
   • Right-click the column Value → choose Rename → type `Sales`

Optional:

• Change the data type of the new `Sales` column to a number:

1. • Click the small icon in the `Sales` column header (e.g. ABC123) and choose Whole Number

Result: You now have a normalized table where each row represents a single product's sales in a particular month. This structure is ideal for analysis or pivoting in Excel.

Reordering Columns

Steps:

1. In Power Query Editor, click and drag the column header to a new position
2. OR: right-click the column header → choose Move → select desired direction (Left, Right, To Beginning, To End)

Duplicating and Referencing Queries

Steps:

1. In the Queries pane on the left side of Power Query Editor:
   • Right-click the query you want to reuse
2. To create a full copy: choose Duplicate
3. To create a linked version that depends on the original: choose Reference

Additional Exercise: Cleanup and Pivot Prep

Goal: Prepare survey data for pivot table analysis

Steps:

1. Load Survey_Results.xlsx and remove rows with missing values in the satisfaction column
2. Rename the satisfaction column to lowercase (e.g. "satisfaction")
3. Group the table by satisfaction rating:
   • Go to Transform → Group By → select satisfaction → operation: Count Rows
4. Load the final grouped table to a new worksheet in Excel

Extension: Add a Conditional Column

Goal: Categorize satisfaction ratings into "Positive", "Neutral", or "Negative"

Steps:

1. In Power Query Editor → go to Add Column tab → click Conditional Column
2. Set:
   • New column name: `SatisfactionLevel`
   • If Satisfaction = 4 or 5 → then "Positive"
   • Else if Satisfaction = 3 → then "Neutral"
   • Else → "Negative"
3. Click OK

---

→ Continue to Module 4: Automation and Parameters

Return to Main Page

• Module 1: Introduction to Power Query
• Module 2: Importing and Managing Data
• Module 3: Data Transformation Techniques
• Module 4: Automation and Parameters
• Module 5: Power Pivot Overview
• Module 6: Summary and Final Exercises