Step-by-Step Guide: Beans Dataset Cleaning

The Goal

Building a Star Schema

The purpose of this exercise is to transform raw, messy data into a Star Schema—the gold standard for analytical data modeling. A star schema simplifies complex data by organizing it into two distinct types of tables: a central Fact Table surrounded by descriptive Dimension Tables.

Fact Tables (The Events)

The center of the star. These tables record measurable, quantitative events (like logged hours or billed amounts). Fact tables primarily contain numeric measures and foreign keys to dimension tables, with minimal descriptive attributes. While they might contain operational flags, timestamps, or degenerate dimensions (like transaction IDs), most descriptive text should live in dimension tables.

Dimension Tables (The Context)

These tables provide the "who, what, when, where, and why." They contain descriptive text and time-related fields (like Employee Names, Task Descriptions, Date attributes, or Client Locations). They give meaning and filters to your metrics.

By isolating descriptions into Dimension tables and keeping primarily numbers and IDs in the Fact table, you ensure your data model is fast, avoids data duplication, and is incredibly easy to filter and analyze. Keep this architecture in mind as you complete the cleaning steps below.

How to use this guide

Read each task, think through how you would solve it in Power Query, then click "Reveal Answer" to check your logic against the guided steps. Check off each item as you complete it in your own file.

1Simple ETL & Setup

1. Open & Profile the Dataset

Task: Open the Beans.xlsx file in Power Query. How do you make sure profiling checks the entire dataset rather than just a sample?

Open Beans.xlsx and choose Transform Data.
At the bottom-left of Power Query Editor, change Column profiling based on top 1000 rows to entire data set.
Use the profile indicators to review the full workbook before cleaning.

Transform Data — Open the workbook and enter Power Query.

Entire dataset profiling — Switch profiling to the full dataset.

2. Remove Irrelevant Data

Task: Identify fields that do not support the later analysis and remove them before modeling.

Remove columns that do not add analytical value for the task. For example, Age is rarely useful as a standalone numeric in this specific analysis context, so remove it from the demographic view before continuing.

Remove Age — Remove the irrelevant field before continuing.

3. Rename Ambiguous Columns

Task: Generic names like ID or short abbreviations create confusion in the model. Rename them before relationships are formed.

CL Table: ID → Client ID

E-Dem Table: CertList → Certification List

Employee Table: MS → Marital Status, ID → Employee ID

Service Table: ID → Service ID, ID_1 → Task ID, Employee → Employee ID, Client → Client ID

Rename columns — Rename business keys clearly.

Remove empty columns — Also remove fully empty columns where shown.

4. Split Composite Name Fields

Task: Separate combined name fields so the final table has cleaner atomic columns.

In the Employee table, split Name using Split Column by Delimiter, then rename the resulting fields to First Name and Last Name.

Split by delimiter — Split the combined name field.

Rename split name columns — Finish the name cleanup.

5. Hunt Down Outliers

Task: Sort and inspect time values so extreme errors are corrected or removed before analysis.

Sort Actual Time descending to isolate unrealistic values.

Correct the typo where 25 should become 2.5, then remove the obvious bad top rows (extreme values that break logic).

Remove top rows — Fix the typo and remove the remaining extreme values.

6. Standardize Inconsistent Values

Task: Replace inconsistent abbreviations and missing-value patterns with one standard business format.

University: Replace short acronyms with their full school names (e.g., standardizing "UW" to "University of Washington").
Job Title: Standardize inconsistent senior-manager labels into one clean spelling.
Marital Status: Replace letters and blanks with full terms such as Married, Single, and Unknown.

Replace values — Use replace values to standardize categories.

7. Add Logic Checks with Conditional Columns

Task: Create text-based validation flags for actual hours and budget comparisons.

Column 1: ActualhoursValid
If Actual Time is within the acceptable range, label it Valid. Otherwise label it Invalid.

Column 2: Above/Below Budget
Compare Actual Time against Budgeted Time and label each record as Above Budget, At the Budget, or Below Budget.

Set these conditional columns to Text.

ActualhoursValid conditional column — If Actual Time is within the acceptable range, label it Valid. Otherwise label it Invalid.

2Table Transformation

8. Give Queries Clear Names

Task: Rename the imported queries so the final model uses readable business names.

Rename the short query names to clear labels such as Client and Employee Demographic before you start merging or building dimensions.

3Data Modeling

9. Merge Employee Data with Fuzzy Matching

Task: Employee information is split across tables. Merge them while allowing imperfect name matches.

Use Employee as the primary table and merge in Employee Demographic.

Turn on Use fuzzy matching and set the similarity threshold to 0.4.

Expand only Certification List and University, and uncheck the prefix option.

Right-click the intermediate demographic query and uncheck Enable load.

Fuzzy match — Use fuzzy matching for the merge.

Expand merged table — Open the expand selector after merging.

Select merged columns — Keep only the two needed fields.

Disable load — Disable the helper query after combining.

10. Build the Task Dimension

Task: Extract descriptive task attributes from the Fact table (Service) into a cleaner Dimension table. The Fact table should only keep the ID.

Duplicate Service and rename the new query Task.

Keep Task ID, Area, and Task Name, then remove duplicates based on Task ID.

Return to Service and remove the duplicated descriptive task fields (Area, Task Name) to strip it down to just the IDs and metrics.

Create Task — Build the Task query from Service.

Remove duplicates Task ID — Deduplicate the Task dimension.

11. Build the Certifications Table

Task: Convert comma-separated certification lists into a normalized table with one certification per row.

Duplicate Employee and rename it Certifications.

Keep only Employee ID and Certification List.

Split Certification List by comma, then select Employee ID and choose Unpivot Other Columns.

Delete Attribute, rename Value to Certificate, and use Trim to remove stray spaces.

Finally, remove Certification List from the main Employee table.

Start certifications table — Start the Certifications query.

Split certifications — Split the multi-value list.

Unpivot other columns — Unpivot into one row per certificate.

Trim certificate field — Trim and finish the dimension table.

12. Close & Apply, Then Form Relationships

Task: Load the cleaned tables and connect the fact table (Service) to the dimension tables using the foreign keys.

Choose Close & Apply.

In Model view, connect the IDs from your Fact table (Service) to your Dimension tables (Task, Client, Employee). Don't forget to connect Date in Service to Date in Calendar.

Create relationship — Link Service Date to Calendar Date.

Final model view — Review the final Star Schema layout.