Lab 2: Data Import & Cleaning with Store Sales Orders

Lab Exercises

The following exercises guide you through importing, cleaning, and loading the Store Sales Orders data with Power Query. Each exercise includes a question, step-by-step instructions, and a brief answer. Complete them in order; later steps assume earlier transformations.

Part A: Import Data

Exercise A1: Import from CSV

Question: Import the Store Sales Orders data from CSV and open the Power Query Editor. What do you see in the preview (column types, obvious issues)?

Instructions:

1. Excel: Data → Get Data → From File → From Text/CSV.
2. Select Lab2-StoreOrders.csv (from this folder or where you saved it). Click Transform Data (not Load) so you open the Power Query Editor before loading.
3. In the Power Query Editor, check the data types shown in the column headers (ABC, 123, calendar icons). Scroll through the table and look for duplicates, blank rows, extra spaces in text, and missing values.

Explanation: Using Transform Data instead of Load lets you clean the data first. The preview shows how Excel interprets each column; types may be wrong (e.g. dates or numbers as Text) and need fixing.

Answer: You should see columns OrderID, OrderDate, Customer, Category, Amount, Quantity, Region, Notes. Many rows are present (200+). OrderDate is typically detected as Text; Amount and Quantity may be Text in several rows. You will notice duplicate rows, fully blank rows, leading/trailing spaces in Customer, Category, and Region, and missing values in Region and Notes.

Exercise A2: Import from Excel

Question: Import the same dataset from the Excel workbook and open the Power Query Editor.

Instructions:

1. Excel: Data → Get Data → From File → From Excel Workbook.
2. Select Lab2-StoreOrders.xlsx, then choose the Orders sheet (or table). Click Transform Data.
3. Confirm the structure (columns and row count) matches the CSV import.

Explanation: Importing from Excel often preserves more type information than CSV, but you still need to verify. Use one source (CSV or Excel) consistently for Parts B–D.

Answer: The Orders sheet has the same columns and rows as the CSV. You can use either source for the cleaning exercises; stick to one for the rest of the lab.

Exercise A3 (optional): Import from Web

Question: Import a small table from a web page using Get Data → From Web.

Instructions:

1. Excel: Data → Get Data → From Other Sources → From Web.
2. Enter a URL that contains an HTML table (e.g. a simple reference or demo table). Use Transform Data to preview and clean if needed.
3. Document the URL and the steps you used.

Answer: "Import from Web" is covered in the Week 3 slides. Use any stable URL with a table; the goal is to practice the From Web workflow and see how Power Query lists available tables.

Exercise A4: Compare CSV vs Excel Import

Question: If you imported both CSV and Excel, compare the two queries. Do the initial detected data types or row counts differ? Why might they?

Instructions:

1. Create two queries: one from Lab2-StoreOrders.csv, one from Lab2-StoreOrders.xlsx (both with Transform Data).
2. In each, note the detected types for OrderDate, Amount, and Quantity, and the row count at the bottom.
3. Briefly note any differences.

Explanation: CSV has no type information; Excel infers from cell formats. Excel workbooks can preserve Number/Date formats, so Power Query may detect them more accurately. Row counts should match if the sources are the same.

Answer: Row counts should match. CSV often detects OrderDate, Amount, or Quantity as Text more frequently; the Excel import may show Date or Number for some columns initially. Both sources require verifying and possibly changing types in Power Query.

Part B: Power Query Transformations

Exercise B1: Change Data Types

Question: Set correct data types for OrderDate, Amount, and Quantity.

Instructions:

1. Select the OrderDate column. Transform → Data Type → Date (or Date/Time). Choose the correct locale if prompted (e.g. day/month/year vs month/day/year).
2. Select Amount → Transform → Data Type → Decimal Number (or Currency if you prefer).
3. Select Quantity → Transform → Data Type → Whole Number.
4. Check for any columns still detected as Text that should be numeric or date; fix them.

Explanation: Correct types ensure filtering, sorting, and calculations work properly. Wrong types (e.g. numbers as text) cause incorrect sums or sorts.

Answer: After changing types, OrderDate supports date filters and sorting; Amount and Quantity can be used in calculations and aggregates. The Applied Steps panel will show "Changed Type" steps for each column you modified.

Exercise B2: Remove Duplicates

Question: Remove duplicate rows and note how many rows were removed.

Instructions:

1. Select all columns (or the key columns you use for duplicates, e.g. OrderID).
2. Home → Remove Rows → Remove Duplicates.
3. Check the row count before and after; record the number of rows removed.

Explanation: Duplicates inflate counts and can bias analysis. Remove them based on a meaningful key (e.g. OrderID) or on all columns, depending on your definition of a duplicate.

Answer: The dataset contains duplicate rows. After removing duplicates, the row count decreases. The exact number depends on whether you use all columns or only OrderID (or another key). Record your before/after counts for your report.

Exercise B3: Filter Rows

Question: Use the column filter dropdowns to inspect or exclude certain values (e.g. blanks or a specific Category/Region).

Instructions:

1. Click the filter icon on a column header (e.g. Category or Region).
2. Use Text Filters or "Remove Empty" (or equivalent) to filter out blanks if needed, or filter to a single category for inspection.
3. Remove or adjust the filter as needed for later steps (e.g. you may remove blank rows via Home → Remove Rows → Remove Blank Rows instead).

Explanation: Filtering helps you explore subsets and confirm where blanks or odd values appear. Use it before standardizing categories or removing blank rows.

Answer: Filtering helps you inspect subsets of the data and confirm blanks or specific categories. You can clear the filter and use Remove Blank Rows for a permanent change, or keep filters for ad-hoc exploration.

Exercise B4: Replace Values

Question: Standardize the Category column (e.g. replace "ELECTRONICS", " electronics " with "Electronics").

Instructions:

1. Select the Category column. Transform → Replace Values.
2. Replace each variant (e.g. "ELECTRONICS", " electronics ") with the standard form "Electronics". Repeat for other categories (Clothing, Food, Books, Sports) as needed.
3. Alternatively, use a Conditional Column or multiple Replace Values steps.

Explanation: Inconsistent categories split groups in PivotTables and reports. Standardizing them ensures correct aggregation and filters.

Answer: After replacing values, Category has consistent capitalization and spacing. You may need several Replace Values steps (one per variant) or a Conditional Column that maps variants to standard names.

Exercise B5: Trim and Clean

Question: Apply Trim to Customer, Category, and Region; use Clean if you encounter non-printable characters.

Instructions:

1. Select Customer. Transform → Format → Trim. Repeat for Category and Region.
2. If any column has non-printable characters (e.g. from imported web or text data), use Transform → Format → Clean on that column.

Explanation: Trim removes leading and trailing spaces; Clean removes non-printable characters (e.g. tab, vertical tab). Both improve matching and grouping.

Answer: Trim removes leading and trailing spaces; Clean removes non-printable characters. Apply them to text columns that come from external sources to avoid duplicate categories or failed lookups.

Exercise B6: Duplicates by Key vs All Columns

Question: Try removing duplicates first by OrderID only, then (in a new query or after undoing) by all columns. Compare the number of rows removed in each case. When would you use each approach?

Instructions:

1. Remove duplicates based only on OrderID. Note the row count before and after.
2. Start again from the source (or use a duplicate of the query) and remove duplicates based on all columns. Note the row count before and after.
3. Compare and briefly explain when to use key-only vs all-column duplicate removal.

Explanation: Key-only (e.g. OrderID) treats two rows as duplicates if the key is the same, even if other columns differ. All-column duplicate removal keeps only rows that are identical in every column. Use key-only when the key uniquely identifies a record; use all-column when you want to drop only exact duplicates.

Answer: Removing duplicates by OrderID only removes more rows if some duplicates have identical OrderIDs but different values elsewhere (e.g. typo in one column). Removing by all columns removes only rows that are identical in every column. Use OrderID when it is the true unique identifier; use all columns when you want to eliminate only exact duplicate rows.

Part C: Missing Values

Exercise C1: Remove Blank Rows

Question: Remove fully blank rows and note the change in row count.

Instructions:

1. Home → Remove Rows → Remove Blank Rows.
2. Compare the row count before and after.

Explanation: Blank rows can break PivotTables and formulas. Remove them when they add no information.

Answer: Blank rows are removed. The number of rows deleted equals the number of fully blank rows in the table. Record this for your cleaning documentation.

Exercise C2: Flag Missing Notes

Question: Add a conditional column that flags rows where Notes is null or blank, without deleting those rows.

Instructions:

1. Add Column → Conditional Column.
2. Set Column name (e.g. Notes_Missing). If Notes is null or equals "", output Yes (or True); otherwise No (or False).
3. Load the query and confirm the new column appears.

Explanation: Flagging missing values preserves all rows for analysis while making it easy to filter or report on missingness.

Answer: The new column identifies rows with missing Notes. You can use it for analysis or reporting while keeping all rows. Similar logic can be applied to Region or other columns if you choose to flag rather than delete.

Exercise C3: Document Your Strategy

Question: In one or two sentences, state how you handled missing values in Region and Notes (e.g. removed blank rows, flagged missing, left as-is).

Answer: Example: "We removed fully blank rows, flagged missing Notes with a conditional column, and kept Region as-is (including blanks) for filtering. We did not impute values." Your statement should match what you actually did in the query.

Exercise C4: When to Impute, Flag, or Delete

Question: Briefly explain when you would (a) delete rows with missing values, (b) flag missing values with a new column, or (c) impute (e.g. fill with mean/median/mode). Give one example each.

Instructions:

1. Consider amount of missing data, whether it’s random or systematic, and how the variable is used.
2. Write 1–2 sentences for each of (a), (b), and (c) with a concrete example.

Explanation: Deletion is simple but loses data; use when missing is small and random. Flagging keeps all rows and supports analysis of missingness. Imputation preserves sample size but can bias results if done poorly; use when justified and documented.

Answer: (a) Delete: When missing is a small proportion and effectively random (e.g. a few scattered blanks in a large survey). (b) Flag: When you need to keep all rows but also analyze or filter by missingness (e.g. flag missing Notes, then filter to "Notes_Missing = Yes" for a subset analysis). (c) Impute: When the variable is important, missing is modest, and you have a defensible method (e.g. fill missing Region with mode, or missing numeric values with column median), and you document the choice.

Part D: Load and Refresh

Exercise D1: Load to Worksheet

Question: Load the cleaned data into an Excel table in a new sheet.

Instructions:

1. In the Power Query Editor, Close & Load.
2. Ensure the query has a clear name (e.g. StoreOrders_Cleaned). Rename it in Queries & Connections if needed.

Answer: The cleaned data appears as a table in a new worksheet. You can use it for further analysis, PivotTables, or charts.

Exercise D2 (optional): Load to Data Model

Question: Create a connection that loads the data into the Data Model only (no worksheet).

Instructions:

1. Duplicate the query or create a new one from the same source. Close & Load To…
2. Choose Only Create Connection and check Add this data to the Data Model.
3. Confirm in Data → Queries & Connections that the query exists and is connected to the Data Model.

Answer: The data is available for PivotTables and DAX via the Data Model, without a separate table on a sheet. Useful for larger datasets and multi-table models.

Exercise D3: Refresh Data

Question: Refresh the query after changing the source file and confirm the change appears.

Instructions:

1. Close the workbook or ensure the source CSV/Excel is not locked. Add a new row to the source file (or change an existing value), then save.
2. In Excel, Data → Refresh All (or right-click the query → Refresh).
3. Verify that the updated data appears in the loaded table.

Answer: Refresh replays the query steps on the current source. Use it when source files are updated regularly (e.g. daily exports). The connection stores the file path; refresh uses that path.

Exercise D4: Connection and Refresh Settings

Question: Where can you change the source file path or refresh settings for a query? What happens if you move or rename the source file?

Instructions:

1. Open Data → Queries & Connections. Right-click the query → Edit or Properties (depending on your Excel version).
2. Look for Source or Connection settings. Note how the path is stored.
3. Move or rename the source file, then try Refresh. Observe the result.

Explanation: The connection stores the full path to the source. If you move or rename the file, Refresh fails until you update the path (e.g. via Data Source Settings or by editing the query and changing the source step).

Answer: You can change the source path via the query’s Source step in Power Query or via Data Source Settings (Data → Get Data → Data Source Settings). If the file is moved or renamed, Refresh fails with an error; update the path and refresh again.