Labs · Power BI

Lab: Fix 10 Common Data Problems in One Customer File – Spaces, Case, Duplicates, Nulls and Split Columns

By Ravindra Bagale · Last updated: 8 October 2026

Suppose we are…

Suppose we work in the CRM team of Lenskart. Store staff in different cities type new customers into a shared sheet, and marketing wants to send a birthday-month offer. Before any campaign, the list must be clean: one row per customer, proper names, a 10-digit mobile number, a lower-case email and a real date.

Our export messy_customers.csv has 12 rows and these 10 problems:

1. Extra spaces at the start, at the end and in the middle of names.
2. Wrong case: RAHUL patil, anita desai, pune.
3. Two values in one column: Pune, Maharashtra.
4. A blank Location.
5. Five different phone formats: +91 98200 11111, 98200-22222, +91-98200-44444…
6. Emails in mixed case: PRIYA@EXAMPLE.COM.
7. Gender codes M, m, F, f.
8. The text N/A and a blank where a date should be.
9. Dates as text in dd/mm/yyyy.
10. Two duplicate rows (C002 and C005 appear twice).

All names, numbers and emails are made up for practice (the emails use the reserved example.com domain).

Goal of this lab

By the end you will have:

• A query Customers with 10 rows and 8 clean columns: ID, Name, City, State, Phone, Email, Gender, JoinDate.
• Fixed each of the 10 problems with a Power Query button, so the same fixes run on every refresh.

What you need (all free)

• Power BI Desktop.
• The file: Download messy_customers.csv (12 rows, 10 problems)
• 40 minutes.

The data: before and after

Before. Twelve rows. Rows 4 and 9 are exact copies of other rows.

After. Ten clean customers.

The formula

Each button writes one line of M. These are the five functions doing most of the work:

Order matters. Trim before you remove duplicates (otherwise "RAHUL patil " with a space and without it look different), and replace N/A before you change JoinDate to a date (otherwise you get Error).

Steps

1. Get data → Text/CSV → messy_customers.csv → Transform Data. In Applied Steps, delete the automatic Changed Type step with its X (it may read 03/01/2025 the US way). Keep Promoted Headers (if it is missing, click Home → Use First Row as Headers).

   👁 What you should see: 12 rows and 7 columns, all with the ABC (text) icon.

2. Problem 1, spaces. Click the Name header, then Transform → Format → Trim. Then Home → Replace Values: Value To Find = two spaces, Replace With = one space, OK.

   👁 What you should see: "Priya Sharma" and "Meera Iyer" start at the left edge; "Vikram Joshi" has one space.

3. Problem 2, case. With Name still selected, click Transform → Format → Capitalize Each Word.

   👁 What you should see: Rahul Patil, Anita Desai, Sneha Kulkarni.

4. Problem 4, blank location (we fix it before splitting). Click Location, then Home → Replace Values. Leave Value To Find empty, type Unknown, Unknown in Replace With, open Advanced options, tick Match entire cell contents, OK.

   👁 What you should see: C004 now shows "Unknown, Unknown".

5. Problem 3, two values in one column. With Location selected, click Home → Split Column → By Delimiter, choose Comma, Each occurrence of the delimiter, OK. Rename the new columns to City and State (double-click each header).

6. Select City and State together (Ctrl+click), then Transform → Format → Trim and Transform → Format → Capitalize Each Word.

   👁 What you should see: City = Pune, Mumbai, Nagpur…; State = Maharashtra, Gujarat… with no leading space; "pune" and "mumbai" are now "Pune" and "Mumbai".

7. Problem 5, phones. Click Phone and do Replace Values three times, leaving Replace With empty each time: find a space , then -, then +91.

   👁 What you should see: every phone is exactly 10 digits, for example 9820011111 and 9820088888.

8. Problem 6, email. Click Email → Transform → Format → lowercase.

9. Problem 7, gender. Click Gender → Transform → Format → UPPERCASE. Then Replace Values: M → Male and F → Female, each time with Match entire cell contents ticked.

   👁 What you should see: only "Male" and "Female" in the column.

10. Problem 8, N/A and blank. Click JoinDate → Replace Values: N/A → null (type the word null), Match entire cell contents ticked. Do it again with Value To Find empty → null.

    👁 What you should see: C003 and C009 show null in italics.

11. Problem 9, dates. Right-click JoinDate → Change Type → Using Locale…, choose Date and English (India), OK.

    👁 What you should see: a calendar icon; C001 is 3 January 2025 (not 1 March); no Error cells. If any cell says Error, right-click the header → Replace Errors → type null.

12. Problem 10, duplicates. Click the CustomerID header, then Home → Remove Rows → Remove Duplicates.

    👁 What you should see: 10 rows (the status bar at the bottom left says 10 rows).

13. Rename CustomerID to ID, rename the query to Customers and click Close & Apply. Build a Table visual with State and Count of ID.

    👁 What you should see: Maharashtra 6, Gujarat 1, Karnataka 1, Telangana 1, Unknown 1, Total 10.

14. Save as Lab-07-clean-customers.

Common mistakes

Self-check checklist

Try-at-home challenge

Marketing wants to send SMS only to valid numbers. Add a column PhoneOK that says TRUE when the phone has exactly 10 digits and starts with 6, 7, 8 or 9 (Indian mobile rule). Hint: Add Column → Custom Column.

= Text.Length([Phone]) = 10 and List.Contains({"6","7","8","9"}, Text.Start([Phone], 1))

Samjla ka? Trim, case, split, replace, then remove duplicates last, and every fix repeats on refresh. Aata pudhe jaauya: add a Profit column twice, in Power Query and in DAX.