Assignments

Lab 2 — Data Import & Cleaning with Store Sales Orders

Due

Sunday of Week 4, 23:59

Released

Week 4

Weight

Part of 20% Participation & Homework

CLOs

1, 6

Goal

Use Microsoft Excel's Power Query (Get Data) to import the Store Sales Orders dataset, diagnose and fix data quality issues, and load a clean, analysis-ready table. This lab is Excel-specific — Power Query is not available in Google Sheets.

Data

Lab2-StoreOrders.csv and Lab2-StoreOrders.xlsx — download from Google Drive.

Variables: OrderID (Text), OrderDate (Date — stored as text in source), Customer, Category, Amount (Decimal — some stored as text), Quantity (Integer — some stored as text), Region, Notes.

Intentional data quality issues for practice:

• Duplicate rows
• OrderDate, Amount, Quantity stored as wrong types
• Leading/trailing spaces in Customer, Category, Region
• Inconsistent Category values (e.g. "ELECTRONICS", " electronics ")
• Blank rows and missing values in Region and Notes

Tasks

Part A — Import

1. Import Lab2-StoreOrders.csv: Data → Get Data → From File → From Text/CSV → Transform Data.
2. Import Lab2-StoreOrders.xlsx: Data → Get Data → From File → From Excel Workbook → select the Orders sheet → Transform Data.
3. Use either source for Parts B–D; close the other with Close & Load To… → Only Create Connection.

Part B — Transformations

Apply all of the following in the Power Query Editor (each becomes one Applied Step):

1. Change types: OrderDate → Date (choose correct locale if prompted), Amount → Decimal Number, Quantity → Whole Number.
2. Remove Duplicates: Home → Remove Rows → Remove Duplicates (use all columns or OrderID alone — note the difference).
3. Replace Values: standardise Category — replace "ELECTRONICS", " electronics ", "ELECTRONICS ", etc. with "Electronics". Repeat for other categories as needed.
4. Trim & Clean: Transform → Format → Trim on Customer, Category, and Region. Apply Clean if non-printable characters are suspected.
5. Remove Blank Rows: Home → Remove Rows → Remove Blank Rows. Note the before/after row count.

Part C — Missing values

1. Remove Blank Rows (already done in B5 — confirm the count).
2. Flag missing Notes: Add Column → Conditional Column → name it Notes_Missing; set to "Yes" if Notes equals null or is blank, otherwise "No".
3. Document your strategy: In one or two sentences in your submission, state how you handled missing values in Region and Notes.

Part D — Load and refresh

1. Load to worksheet: Close & Load → the cleaned table appears as an Excel table on a new sheet. Rename the query StoreOrders_Cleaned.
2. Refresh: Make a small change to the source CSV (add one row), save, then Data → Refresh All. Confirm the new row appears.

Submission

Submit one Excel workbook (.xlsx) to the LMS:

• A sheet with the cleaned StoreOrders_Cleaned table (loaded from Power Query).
• The Queries & Connections panel visible (or a screenshot) showing the query steps.
• A text cell or comment documenting your missing-value strategy.

Group Progress Report 2 is also due this week. Submit separately:

• Data quality report (missing values, outliers, inconsistencies)
• Cleaning methodology (decisions made and rationale)
• Cleaned dataset with data dictionary (1–2 pages + Excel file)