Why Power Query transforms messy data in Excel - Business Case Study

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants you to clean and organize sales data that is messy and inconsistent so it can be used for accurate reporting.

📊 Data: You have a sales data file with columns for Date, Product, Region, Sales Amount, and Notes. The data has inconsistent date formats, extra spaces, mixed case text, and some missing values.

🎯 Deliverable: A clean and organized table with consistent date format, trimmed text, standardized case, and no missing values in key columns.

Sample Data

Date	Product	Region	Sales Amount	Notes
1/5/2024	apple	north	100	First sale
2024-01-06	Banana	South	200
07-Jan-2024	APPLE	North	150	Repeat
1/8/24	banana	south		Missing sale
2024/01/09	Orange	East	120
10-Jan-2024	orange	East	130	Good sale
11/01/2024	Apple	North	170
2024-01-12	Banana	South	210	New stock

1

Step 1: Load the messy sales data into Power Query from Excel.

In Excel, select the data range, then go to Data > From Table/Range to open Power Query Editor.

Expected Result

The data appears in Power Query Editor ready for transformation.

2

Step 2: Standardize the Date column to a consistent date format.

Select the Date column, then choose Transform > Data Type > Date.

Expected Result

All dates are converted to Excel date format, e.g., 01/05/2024, 01/06/2024, etc.

3

Step 3: Trim extra spaces from the Product column.

Select the Product column, then choose Transform > Format > Trim.

Expected Result

Product names have no leading or trailing spaces.

4

Step 4: Convert all text in the Product and Region columns to proper case (first letter capital).

Select Product and Region columns, then Transform > Format > Capitalize Each Word.

Expected Result

Product and Region names are standardized, e.g., 'Apple', 'Banana', 'North', 'South'.

5

Step 5: Remove rows where Sales Amount is missing.

Filter the Sales Amount column to remove blank or null values.

Expected Result

Rows with missing sales amounts are removed from the data.

6

Step 6: Close and load the cleaned data back into Excel as a new table.

Click Home > Close & Load to return the cleaned data to Excel.

Expected Result

A new clean table appears in Excel with consistent dates, trimmed and capitalized text, and no missing sales amounts.

Final Result

Date       | Product | Region | Sales Amount | Notes
-----------------------------------------------------
01/05/2024 | Apple   | North  | 100          | First sale
01/06/2024 | Banana  | South  | 200          | 
01/07/2024 | Apple   | North  | 150          | Repeat
01/09/2024 | Orange  | East   | 120          | 
01/10/2024 | Orange  | East   | 130          | Good sale
01/11/2024 | Apple   | North  | 170          | 
01/12/2024 | Banana  | South  | 210          | New stock

✓Dates are now consistent and easy to sort or filter.

✓Product and Region names are standardized for accurate grouping.

✓Rows with missing sales amounts are removed to avoid errors in analysis.

✓The cleaned data is ready for reliable reporting and charts.

Bonus Challenge

Add a new column in Power Query that categorizes sales as 'High' if Sales Amount is above 150, otherwise 'Low'.

Show Hint

Use Add Column > Conditional Column in Power Query and set condition: Sales Amount > 150 then 'High' else 'Low'.

Practice

1. Why do people use Power Query in Excel when working with messy data?

easy

A. To clean and organize data easily without complex formulas

B. To create charts and graphs automatically

C. To write VBA macros for automation

D. To send emails directly from Excel

Solution

1. Step 1: Understand Power Query's main purpose

   Power Query is designed to clean and transform messy data into a usable format.

2. Step 2: Compare options with Power Query features

   Creating charts, writing macros, or sending emails are not Power Query's main functions.

3. Final Answer:

   To clean and organize data easily without complex formulas -> Option A

4. Quick Check:

   Power Query = Easy data cleaning [OK]

Hint: Power Query cleans data without formulas [OK]

Common Mistakes:

• Confusing Power Query with chart tools
• Thinking Power Query writes macros
• Assuming Power Query sends emails

2. Which of these is the correct way to start Power Query in Excel?

easy

A. Open the Review tab and select Spell Check

B. Press Ctrl + C to copy data

C. Use the Insert tab to add a new chart

D. Go to Data tab and click Get & Transform Data

Solution

1. Step 1: Identify where Power Query is located

   Power Query is accessed from the Data tab under Get & Transform Data.

2. Step 2: Eliminate unrelated options

   Copying data, spell check, and inserting charts are unrelated to starting Power Query.

3. Final Answer:

   Go to Data tab and click Get & Transform Data -> Option D

4. Quick Check:

   Power Query start = Data tab [OK]

Hint: Power Query is under Data tab in Excel [OK]

Common Mistakes:

• Trying to start Power Query from Insert tab
• Confusing copy shortcut with Power Query
• Looking in Review tab for Power Query

3. You load a table with extra spaces and inconsistent capitalization into Power Query. After applying the 'Trim' and 'Lowercase' steps, what will the data look like?

medium

A. Data with spaces removed but original capitalization kept

B. Data unchanged with spaces and mixed case

C. Data with no extra spaces and all letters in lowercase

D. Data converted to uppercase with spaces kept

Solution

1. Step 1: Understand the 'Trim' step effect

   'Trim' removes extra spaces from text values in Power Query.

2. Step 2: Understand the 'Lowercase' step effect

   'Lowercase' converts all text letters to lowercase.

3. Final Answer:

   Data with no extra spaces and all letters in lowercase -> Option C

4. Quick Check:

   Trim + Lowercase = clean lowercase text [OK]

Hint: Trim removes spaces; Lowercase changes text to small letters [OK]

Common Mistakes:

• Thinking Trim changes capitalization
• Assuming Lowercase removes spaces
• Believing data stays unchanged

4. You tried to remove duplicates in Power Query but still see repeated rows. What is the likely mistake?

medium

A. You removed duplicates before trimming spaces, so spaces differ

B. You applied the Remove Duplicates step twice

C. You forgot to load the data back to Excel

D. You used the Filter Rows step instead of Remove Duplicates

Solution

1. Step 1: Understand how spaces affect duplicates

   Extra spaces make rows look different, so duplicates remain if not trimmed first.

2. Step 2: Check the order of steps

   Removing duplicates before trimming spaces won't remove all duplicates correctly.

3. Final Answer:

   You removed duplicates before trimming spaces, so spaces differ -> Option A

4. Quick Check:

   Trim first, then remove duplicates [OK]

Hint: Trim spaces before removing duplicates [OK]

Common Mistakes:

• Removing duplicates before cleaning data
• Confusing Filter Rows with Remove Duplicates
• Not loading data back to Excel

5. You have a messy sales data table with missing dates, extra spaces, and inconsistent product names. Which sequence of Power Query steps will best clean this data?

hard

A. Remove duplicates, Change product names to uppercase, Trim spaces, Remove rows with missing dates

B. Remove rows with missing dates, Trim spaces, Change product names to lowercase, Remove duplicates

C. Change product names to lowercase, Remove duplicates, Remove rows with missing dates, Trim spaces

D. Trim spaces, Remove duplicates, Remove rows with missing dates, Change product names to uppercase

Solution

1. Step 1: Remove rows with missing dates first

   Missing dates cause incomplete data, so remove those rows early.

2. Step 2: Trim spaces and standardize product names

   Trim spaces to clean text, then convert product names to lowercase for consistency.

3. Step 3: Remove duplicates last

   After cleaning, duplicates can be accurately identified and removed.

4. Final Answer:

   Remove rows with missing dates, Trim spaces, Change product names to lowercase, Remove duplicates -> Option B

5. Quick Check:

   Clean data stepwise: remove blanks, trim, standardize, then dedupe [OK]

Hint: Clean missing data first, then trim and standardize before deduplication [OK]

Common Mistakes:

• Removing duplicates before cleaning text
• Trimming spaces after removing duplicates
• Changing case inconsistently