SolveWithPowerBI

Power BI Beginner Challenge 1: Load and Clean Data

Learn how to import raw data into Power BI and clean it using Power Query so it’s ready for analysis and reporting.

This challenge focuses on the most underestimated Power BI skill: data preparation.

Business Scenario

You’ve received a sales export from a colleague.
The data comes from different systems and hasn’t been cleaned.

Before you can build any chart or dashboard, you must:

  • Fix column names
  • Remove empty rows
  • Correct data types
  • Prepare the dataset for analysis

This is exactly what Power BI is used for in real projects.

Dataset

File: sales_raw.xlsx
Sheet: SalesData

Columns (raw state)

  • Order Date (text)
  • Region (text, inconsistent casing)
  • Product (text)
  • Sales Amount (text, contains currency symbols)
  • Quantity (sometimes blank)
  • Empty rows at the bottom

Assume the dataset is messy on purpose.

Your Task

Using Power BI Desktop, clean and prepare the dataset so that:

  1. Column names are consistent and readable
  2. Empty rows are removed
  3. Data types are correctly assigned
  4. Sales values are numeric
  5. The dataset is ready for analysis

Step-by-Step Instructions

1️⃣ Load the Data

  • Open Power BI Desktop
  • Click Get Data → Excel
  • Select sales_raw.xlsx
  • Choose the SalesData sheet
  • Click Transform Data

2️⃣ Clean Column Names

In Power Query Editor:

  • Rename columns to:
    • Order Date
    • Region
    • Product
    • Sales
    • Quantity

Tip: Avoid symbols, currency signs, or inconsistent spacing.

3️⃣ Remove Empty Rows

  • Go to Home → Remove Rows → Remove Blank Rows

This ensures visuals and measures won’t break later.

4️⃣ Fix Data Types

Set correct data types:

  • Order Date → Date
  • Region → Text
  • Product → Text
  • Sales → Decimal Number
  • Quantity → Whole Number

If Sales contains $ or €:

  • Use Transform → Replace Values
  • Remove the currency symbol
  • Then change data type

5️⃣ Handle Missing Quantities

  • Replace blank Quantity values with 0
    • Right-click column → Replace Values
    • Replace null with 0

6️⃣ Apply Changes

  • Click Close & Apply

Your data model now contains clean, usable data.

Expected Result

You should now have:

  • A single clean table
  • Correct data types
  • No empty rows
  • A dataset ready for charts, KPIs, and DAX

No visuals yet—this challenge is about foundation first.

Why This Matters

Power BI dashboards fail more often because of bad data than bad visuals.

Mastering Power Query early:

  • Saves hours later
  • Prevents broken measures
  • Makes your reports scalable

This skill alone separates beginners from professionals.

Bonus (Optional)

Try these if you’re curious:

  • Trim whitespace from Region
  • Standardize region names (e.g. “us”, “US”, “Us” → “US”)
  • Reorder columns logically

➡️ Next Challenge

👉 Challenge #2: Create Your First KPI Card

Now that your data is clean, you’ll build your first Power BI visual using it.