The Project | About Us | Contribute | Donations | License

HOME

Data cleaning and transformation are crucial steps in preparing your data for analysis in Power BI. This module will guide you through the process of using Power Query to clean and transform your data, ensuring it is accurate, consistent, and ready for modeling and visualization.

Key Concepts

Power Query Editor: A tool within Power BI used for data transformation.
Data Cleaning: The process of detecting and correcting (or removing) corrupt or inaccurate records from a dataset.
Data Transformation: The process of converting data from one format or structure into another.

Power Query Editor Overview

The Power Query Editor is where you perform data cleaning and transformation tasks. It provides a user-friendly interface to apply various transformations to your data.

Steps to Access Power Query Editor

Open Power BI Desktop.
Click on the "Home" tab.
Select "Transform Data" to open the Power Query Editor.

Common Data Cleaning Tasks

Removing Duplicates

Duplicates can skew your analysis. Removing them ensures that each record is unique.

Home > Remove Rows > Remove Duplicates

Handling Missing Values

Missing values can be handled by removing rows, replacing them with a default value, or using statistical methods to estimate them.

Transform > Replace Values

Changing Data Types

Ensure that each column has the correct data type (e.g., text, number, date).

Transform > Data Type

Filtering Rows

Remove unnecessary rows based on specific criteria.

Home > Remove Rows > Remove Top Rows / Remove Bottom Rows

Splitting Columns

Split a column into multiple columns based on a delimiter.

Transform > Split Column

Common Data Transformation Tasks

Merging Queries

Combine data from multiple tables into a single table.

Home > Combine > Merge Queries

Appending Queries

Stack data from multiple tables on top of each other.

Home > Combine > Append Queries

Grouping Data

Aggregate data by grouping rows based on one or more columns.

Transform > Group By

Pivoting and Unpivoting Columns

Transform rows into columns and vice versa.

Transform > Pivot Column / Unpivot Columns

Adding Custom Columns

Create new columns based on custom formulas.

Add Column > Custom Column

Practical Example

Let's walk through a practical example of cleaning and transforming data using Power Query Editor.

Example Scenario

You have a dataset containing sales data with the following issues:

Duplicate rows
Missing values in the "Sales Amount" column
Incorrect data types
Need to split the "Full Name" column into "First Name" and "Last Name"

Step-by-Step Solution

Remove Duplicates:
- Open Power Query Editor.
- Select the table.
- Go to Home > Remove Rows > Remove Duplicates.
Handle Missing Values:
- Select the "Sales Amount" column.
- Go to Transform > Replace Values.
- Replace null values with 0.
Change Data Types:
- Select the "Sales Amount" column.
- Go to Transform > Data Type > Decimal Number.
Split Columns:
- Select the "Full Name" column.
- Go to Transform > Split Column > By Delimiter.
- Choose a space as the delimiter.

Code Snippet

Here is a sample M code for the above transformations:

let
    Source = Excel.Workbook(File.Contents("C:\SalesData.xlsx"), null, true),
    SalesData_Sheet = Source{[Item="SalesData",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(SalesData_Sheet, [PromoteAllScalars=true]),
    #"Removed Duplicates" = Table.Distinct(#"Promoted Headers"),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates",null,0,Replacer.ReplaceValue,{"Sales Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Sales Amount", type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Full Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"First Name", "Last Name"})
in
    #"Split Column by Delimiter"

Practical Exercise

Task

Import a dataset into Power BI.
Open Power Query Editor.
Perform the following transformations:
- Remove duplicate rows.
- Replace missing values in a numeric column with the average of that column.
- Change the data type of a date column to Date.
- Split a full name column into first and last names.

Solution

Remove Duplicates:
- Home > Remove Rows > Remove Duplicates
Replace Missing Values:
- Select the numeric column.
- Transform > Replace Values
- Replace null values with the average of the column.
Change Data Type:
- Select the date column.
- Transform > Data Type > Date
Split Columns:
- Select the full name column.
- Transform > Split Column > By Delimiter
- Choose a space as the delimiter.

Summary

In this module, you learned how to use Power Query Editor to clean and transform your data. Key tasks include removing duplicates, handling missing values, changing data types, filtering rows, splitting columns, merging and appending queries, grouping data, pivoting and unpivoting columns, and adding custom columns. These skills are essential for preparing your data for analysis in Power BI.

Data Cleaning and Transformation