Data Cleaning and Transformation Using Power Query - Textnotes

Data Cleaning and Transformation Using Power Query

This module teaches learners how to clean, shape, and transform data using Power Query in Power BI Desktop. Students will understand core transformations, manage applied steps, use the Advanced Editor for M code, and follow best practices for creating clean, reusable datasets. Hands-on exercises reinforce practical application.

1. Removing Rows and Columns

Remove unnecessary rows or columns to reduce dataset size and improve performance.

Example:

  1. A dataset contains empty rows and a “Notes” column that is not required. Use “Remove Rows” to delete blank rows and “Remove Columns” to drop the “Notes” column.

2. Splitting Columns

Split a column into multiple columns based on delimiter, number of characters, or positions.

Example:

  1. Column “Full Name” = “John Doe”
  2. Split by space → Columns: “First Name” = John, “Last Name” = Doe

3. Changing Data Types

Ensure data is in the correct type for calculations and visuals.

Example:

  1. “Order Date” column stored as text → Convert to Date type
  2. “Sales Amount” stored as text → Convert to Decimal Number

4. Merging and Appending Tables

  1. Merge: Combine tables horizontally based on a key
  2. Append: Combine tables vertically

Example Merge:

  1. Orders table + Customer table → Merge on Customer ID to get customer details for each order

Example Append:

  1. January sales + February sales → Append to get combined dataset

5. Conditional Columns

Create new columns based on logical conditions.

Example:

  1. Column “Sales Category”:
  2. If Sales > 10000 → “High”
  3. If Sales 5000–10000 → “Medium”
  4. Else → “Low”

6. Pivoting and Unpivoting Data

  1. Pivot: Transform rows into columns for aggregation
  2. Unpivot: Transform columns into rows for analysis

Example Pivot:

  1. Rows: Product and Month
  2. Pivot Month column → Columns: Jan, Feb, Mar

Example Unpivot:

  1. Columns: Jan, Feb, Mar → Rows: Month column with values

7. Applied Steps and Advanced Editor (M Code Basics)

  1. Applied Steps: Track all transformations sequentially for easy editing or rollback
  2. Advanced Editor: View and edit M code directly for complex transformations

Example:

  1. Applied Steps: Imported CSV → Removed columns → Changed data types
  2. Advanced Editor allows editing the same steps in M code to apply changes programmatically

8. Best Practices for Data Transformation

  1. Remove unnecessary rows and columns early
  2. Rename columns clearly for readability
  3. Document transformation logic in Applied Steps
  4. Keep transformations reusable for future datasets