Data Cleaning and Modeling Assignment
This assignment focuses on preparing reliable data for analysis in Power BI. You will use Power Query to clean source data, organize tables, create relationships, and validate the model before building reports.
Assignment Scenario
The reporting team at CIIT TRANING INSTITUTE has received sales data from several departments. The files contain inconsistent product names, missing values, duplicate records, incorrect data types, and repeated customer information.
Your task is to prepare the data and create a model that can support reliable sales analysis by product, customer, region, and date.
1. Assignment Objectives
- Identify common data quality problems.
- Import source files into Power BI Desktop.
- Clean and transform data using Power Query.
- Set suitable data types and standardize values.
- Separate fact data from dimension data.
- Create and validate table relationships.
- Test the model using simple measures and visuals.
- Document the transformations performed.
Data Preparation Lifecycle
2. Understand the Sample Data
Use a sales transaction table similar to the example below. You can prepare your own workbook with these columns or use an existing practice dataset.
| OrderID | OrderDate | Product | Region | Quantity | UnitPrice |
|---|---|---|---|---|---|
| ORD001 | 2026-01-05 | Laptop | West | 2 | 45000 |
| ORD002 | 2026-01-08 | Monitor | East | 3 | 12000 |
| ORD003 | 2026-02-02 | Keyboard | North | 5 | 1500 |
| ORD004 | 2026-02-10 | Laptop | West | 1 | 45000 |
3. Identify Data Quality Problems
Before changing data, inspect it and identify the issues that may affect calculations or reporting.
| Problem | Example | Possible action |
|---|---|---|
| Inconsistent text | West, west, WEST | Standardize case and whitespace. |
| Missing values | Blank Product | Investigate the source and decide how to handle it. |
| Incorrect data type | Quantity stored as text | Convert to a numeric type after checking values. |
| Duplicate rows | The same transaction appears twice | Confirm whether it is a true duplicate before removing it. |
| Invalid date | 31/02/2026 | Investigate and correct from a reliable source if possible. |
| Unexpected numeric values | Negative quantity | Check whether it represents a return or an error. |
Common Data Quality Issues
4. Import Data into Power Query
- Open Power BI Desktop.
- Select Home → Get data.
- Choose Excel, Text/CSV, or the connector matching your source.
- Select the required table or worksheet.
- Choose Transform Data.
- Inspect the columns, data types, null values, and row counts.
5. Clean and Transform the Data
Step 1: Rename Columns
Use clear, consistent column names such as OrderID, OrderDate, Product, Region, Quantity, and UnitPrice. Avoid unnecessary spaces and ambiguous abbreviations.
Step 2: Remove Extra Spaces
Select text columns and use the Power Query transformation options to apply Trim and, where appropriate, Clean. These operations can remove unwanted spaces and non-printing characters.
Step 3: Standardize Text
Use the Transform ribbon to change text case where appropriate. For business categories such as Region, verify that the standardized values represent the same category before replacing them.
Step 4: Set Data Types
| Column | Recommended type |
|---|---|
| OrderID | Text |
| OrderDate | Date |
| Product | Text |
| Region | Text |
| Quantity | Whole Number |
| UnitPrice | Fixed Decimal Number or Decimal Number |
Step 5: Review Nulls and Errors
Filter for blank values and error rows. Investigate why each value is missing or invalid before deciding whether to replace it, retain it, or exclude the affected record.
Step 6: Review Duplicates
Select the correct business key or set of columns before using Remove Duplicates. Two rows with the same customer or product are not necessarily duplicate transactions.
Cleaning Sequence
6. Separate Fact and Dimension Tables
A data model is easier to maintain when transaction records are separated from descriptive information.
| Table | Example columns | Purpose |
|---|---|---|
| FactSales | OrderID, DateKey, ProductKey, CustomerKey, Quantity, UnitPrice | Stores transaction-level values. |
| DimProduct | ProductKey, ProductName, Category | Describes products. |
| DimCustomer | CustomerKey, CustomerName, Segment | Describes customers. |
| DimDate | DateKey, Date, Month, Year | Supports time-based analysis. |
Example Star Schema
7. Create Relationships
- Open Model view in Power BI Desktop.
- Identify the key column shared by each fact and dimension table.
- Create a one-to-many relationship from the dimension table to the fact table where appropriate.
- Confirm that dimension keys are unique.
- Review relationship direction and active status.
- Test whether filters from dimensions correctly affect the fact table.
8. Create Validation Measures
After cleaning and modeling the data, create a few measures to test whether the model returns sensible results.
Total Quantity
Total Sales
Transaction Rows
Distinct Orders
Use these measures in cards and tables. Compare their results with independent calculations from the source file and investigate any differences.
9. Test the Data Model
- Compare row counts before and after cleaning.
- Check that excluded rows are documented.
- Verify that date values sort chronologically.
- Compare Total Sales against a trusted source total.
- Check whether filtering by product changes sales as expected.
- Verify that distinct orders are not confused with transaction rows.
- Look for blank categories and unexpected totals.
- Record any remaining data limitations.
Validation Checklist
10. Practical Assignment Tasks
Complete the Data Cleaning and Modeling Assignment
- Import the provided sales dataset into Power BI Desktop.
- List at least five data quality problems found in the source data.
- Clean text values and standardize column names.
- Correct data types and investigate null values and errors.
- Review duplicate records using the appropriate business key.
- Separate the transaction table from relevant dimension tables.
- Create relationships in Model view.
- Create Total Quantity, Total Sales, Transaction Rows, and Distinct Orders measures.
- Build a test page with cards and a product or region table.
- Validate the results and document all major transformations.
Required Submission
- Power BI file (.pbix).
- Screenshot of the final data model.
- List of data quality problems discovered.
- Summary of Power Query transformations.
- Screenshot of validation measures or visuals.
- Short explanation of relationship design decisions.
11. Knowledge Check
Question 1: What is the purpose of Power Query?
Power Query is used to connect to data sources and transform data through operations such as filtering, renaming, changing data types, and handling missing or inconsistent values.
Question 2: Should every repeated row be removed?
No. Confirm whether the rows are true duplicates according to the business rules. Similar-looking transactions may be legitimate.
Question 3: What is a fact table?
A fact table typically stores business events or transactions and numeric values that can be analyzed, such as quantities and sales.
Question 4: Why should dimension keys be unique?
Unique dimension keys support the expected one-to-many relationship and help prevent ambiguous or incorrect filtering.
Question 5: How can you check whether a model is correct?
Compare row counts and key totals with the source data, test relationships using filters, and investigate unexpected results.
Summary
In this assignment, you practiced preparing data for Power BI analysis. You learned to identify data quality problems, transform source files with Power Query, separate fact and dimension tables, create relationships, and validate calculations.
- Inspect data before making changes.
- Standardize values and correct data types.
- Investigate missing values and duplicates before removing them.
- Build a clear model with appropriate relationships.
- Validate measures against trusted source totals.
- Document transformations and assumptions.
Reliable reports begin with well-prepared data and a model whose relationships and calculations have been tested.