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

Steps:

1. Go to Excel → Data tab → Get Data → From File → From Workbook
2. Select and load Orders_2023.xlsx
3. In Navigator → select the worksheet → click Transform Data
4. In Power Query Editor:
   • Select all columns representing months (e.g. Jan, Feb, Mar)
   • Go to the Transform tab
   • Click Unpivot Columns
5. This will convert columns into attribute-value pairs

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