TECH **Power BI Data Cleaning: Remove Duplicates, Replace Values, Split Columns, Trim**

Power BI Data Cleaning: Remove Duplicates, Replace Values, Split Columns, Trim

A Hyper-Practical, Zero-Fluff Study Guide

1. What This Is & Why It Matters

You just inherited a Power BI report that’s supposed to track monthly sales performance. The data comes from three different sources: an Excel file from Finance, a CSV export from Salesforce, and a SQL query from the ERP system. When you load it into Power BI, you notice:

• Duplicate rows (same order ID appears twice).
• Inconsistent formatting ("USA" vs. "United States" vs. "US").
• Messy text (" John Doe " instead of "John Doe").
• Combined fields ("Doe, John | [email protected]" instead of separate columns for name and email).

If you don’t clean this data before building visuals, your report will be wrong, slow, and untrustworthy. Stakeholders will question your numbers, your DAX measures will break, and you’ll waste hours debugging why a simple SUM() doesn’t match the source.

This guide teaches you how to:
✅ Remove duplicates (so you don’t double-count sales).
✅ Replace values (so "USA" and "US" don’t create separate categories).
✅ Split columns (so "LastName, FirstName" becomes two clean fields).
✅ Trim whitespace (so " John " becomes "John").

Real-world scenario:
You’re building a customer segmentation dashboard. The raw data has: - Duplicate customer IDs (from a botched CRM export).
- A "Full Name" column with "Doe, John" (needs splitting).
- A "Country" column with "USA", "US", and "United States" (needs standardization).
- Extra spaces in email addresses (causing failed lookups).

If you skip cleaning, your dashboard will show 3x more customers than actually exist, and your "Top 10 Countries" chart will have three separate entries for the U.S..

2. Core Concepts & Components

? Remove Duplicates

• Definition: Identifies and removes rows where all (or some) column values are identical.
• Production insight: Duplicates distort aggregations (SUM, COUNT, AVG). Always check for them before writing DAX measures.

? Replace Values

• Definition: Substitutes one value with another (e.g., "US" → "USA").
• Production insight: Inconsistent values break relationships and filters. Standardize before loading into Power BI.

? Split Columns

• Definition: Divides a single column into multiple columns based on a delimiter (e.g., "Doe, John" → "Doe" and "John").
• Production insight: Splitting is destructive—Power BI doesn’t auto-reverse it. Always duplicate the original column before splitting.

? Trim

• Definition: Removes leading/trailing whitespace from text (e.g., " John " → "John").
• Production insight: Whitespace causes failed joins and incorrect filters. Trim all text columns by default.

? Power Query Editor

• Definition: The tool in Power BI where you clean data before loading it into the model.
• Production insight: Changes in Power Query are non-destructive to the source. Always clean here, not in DAX.

? Applied Steps Pane

• Definition: A history of all transformations applied to your data in Power Query.
• Production insight: If a step breaks, delete it and redo it—don’t edit the M code manually unless you’re advanced.

? M Language

• Definition: The formula language behind Power Query (similar to Excel formulas but more powerful).
• Production insight: You don’t need to write M for basic cleaning, but understanding it helps debug errors.

3. Step-by-Step Hands-On Section

Prerequisites

• Power BI Desktop installed (free version is fine).
• A dataset with at least one of these issues: duplicates, inconsistent values, combined columns, or whitespace.

Task: Clean a Messy Customer Dataset

We’ll use this sample data (copy into Excel or CSV):

Issues to fix:
1. Duplicate CustomerID (1001 appears twice).
2. FullName is "Last, First" (needs splitting).
3. Country has "USA", "US", and "United States" (needs standardization).
4. Email has leading/trailing spaces (needs trimming).

Step 1: Load Data into Power BI

1. Open Power BI Desktop.
2. Click Home → Get Data → Text/CSV.
3. Select your file and click Transform Data (this opens Power Query Editor).

Step 2: Remove Duplicates

1. In Power Query Editor, select the CustomerID column.
2. Click Home → Remove Rows → Remove Duplicates.
3. Why? CustomerID is the unique identifier. Keeping duplicates would double-count this customer.
4. Verify: The row count should drop from 5 to 4.

Step 3: Replace Inconsistent Values

1. Select the Country column.
2. Click Home → Replace Values.
3. In the dialog:
4. Value to Find: US
5. Replace With: USA
6. Click OK.
7. Repeat for:
8. Value to Find: United States
9. Replace With: USA
10. Verify: All Country values should now be USA, Canada, or Mexico.

Step 4: Split the FullName Column

1. Select the FullName column.
2. Click Home → Split Column → By Delimiter.
3. In the dialog:
4. Select or enter delimiter: Comma (,).
5. Split into: Columns.
6. Click OK.
7. Rename the new columns:
8. FullName.1 → LastName
9. FullName.2 → FirstName
10. Trim whitespace from both columns:
11. Select LastName and FirstName.
12. Click Transform → Format → Trim.
13. Verify: LastName and FirstName should now be clean (e.g., "Doe" and "John").

Step 5: Trim Whitespace from Email

1. Select the Email column.
2. Click Transform → Format → Trim.
3. Verify: No leading/trailing spaces (e.g., "[email protected]" instead of " [email protected] ").

Step 6: Load Clean Data into Power BI

1. Click Home → Close & Apply.
2. Your data is now clean and ready for modeling!

4. ? Production-Ready Best Practices

? Always Clean in Power Query, Not DAX

• Why? Power Query is faster and more reliable for cleaning. DAX is for calculations, not transformations.
• Exception: If you need dynamic cleaning (e.g., user selects a filter that changes how data is cleaned), use DAX.

? Duplicate Columns Before Splitting

• Why? Splitting is destructive. If you mess up, you can’t undo it without reloading the data.
• How? Right-click the column → Duplicate Column before splitting.

? Standardize Values Early

• Why? Inconsistent values (e.g., "USA" vs. "US") break relationships and filters.
• How? Use Replace Values or create a mapping table (e.g., a separate table with CountryCode and CountryName).

? Trim All Text Columns by Default

• Why? Whitespace causes failed joins and incorrect filters.
• How? Select all text columns → Transform → Format → Trim.

? Document Your Cleaning Steps

• Why? Future you (or your teammate) will need to understand what you did.
• How? Add a custom step in Power Query with a note: m // Removed duplicates on CustomerID // Standardized Country values to "USA" // Split FullName into LastName and FirstName

? Test with a Small Subset First

• Why? Cleaning a 10M-row dataset takes time. Test on 100 rows first.
• How? Use Keep Rows → Keep Top Rows (e.g., 100) before cleaning.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (PL-300)

Typical Question Patterns

1. "You have a column with 'FirstName LastName'. How do you split it?"
2. Answer: Use Split Column → By Delimiter (space).

3. Trap: If you pick "By Number of Characters," it won’t work for names of different lengths.

4. "A table has duplicate rows. How do you remove them?"

5. Answer: Select the column(s) → Remove Duplicates.

6. Trap: If you don’t select a column, Power BI removes all duplicates (even if some rows are unique in other columns).

7. "A column has 'USA', 'US', and 'United States'. How do you standardize it?"

8. Answer: Use Replace Values to change all to "USA".

9. Trap: If you use Conditional Column, it’s overkill for simple replacements.

10. "A text column has leading/trailing spaces. How do you fix it?"

11. Answer: Transform → Format → Trim.
12. Trap: Clean removes non-printable characters, but Trim only removes spaces.

7. ? Hands-On Challenge (with Solution)

Challenge:

You have a column ProductCode with values like: - "PROD-1001" - "PROD 1002" - "PROD_1003"

Task: Extract just the numeric part (e.g., 1001, 1002, 1003).

Solution:

1. Select the ProductCode column.
2. Click Transform → Extract → Text After Delimiter.
3. Set the delimiter to -, (space), or _ (underscore).
4. Why it works: Power Query extracts everything after the last delimiter, leaving just the number.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Microsoft Power Query Documentation – Official guide to all transformations.
2. Power BI Guided Learning – Cleaning Data – Free Microsoft training.
3. Guy in a Cube – Power Query Tips – Practical video tutorials.
4. SQLBI – Power Query M Reference – Advanced M language guide.