Technical Background & Data Preparation

Before building the visuals and reporting layout, a robust Data Transformation (ETL), Data Modeling, and DAX Calculation process was executed in Power Query and Power BI to ensure data integrity, consistent formatting, and dynamic metric reporting

Raw Multi-Table Data (Customers, Orders, Payments, Products)
├── Power Query ETL Transformation
│     ├── Schema Audit & Column Understanding
│     ├── Deduplication & Data Cleaning
│     ├── Missing Value Imputation (Not Specified / 0)
│     ├── Data Type Validation & Conversions
│     └── Custom Column Feature Engineering (AI Copilot / Power Query M)
├── Star Schema Data Modeling
│     ├── Primary Key & Foreign Key Relationship Setup
│     └── One-to-Many Relationship Enforcements
└── DAX Measures & KPI Engineering
├── Total Metrics (Revenue, Orders, Quantity, Customers, Products)
└── Average Calculations (Avg. Discount, Avg. Unit Price)

1. Schema Understanding & Data Audit Each table within the relational dataset (customers, orders, payments, and products) was systematically reviewed to understand field relationships, key constraints, and business context.

2. Deduplication & Data Cleaning • Executed Remove Duplicates across key entity tables to eliminate redundant records and enforce unique primary keys (CustomerID, OrderID, ProductID).
• Audited records for structural inconsistencies and missing data patterns across columns.

3. Missing Value Imputation • Text Fields: Replaced blank text entries with "Not Specified" to keep visual filters clean and prevent ambiguous missing values.
• Numeric Fields: Replaced null values with 0 in numeric metrics to maintain mathematical integrity across calculations and aggregations.

4. Data Type Validation & Conversion • Audited all field types to ensure accurate storage and performance.
• Formatted ID fields and age metrics as Whole Numbers.
• Formatted price, discount percentages, and monetary values as Decimal Numbers and currency formats (£).

5. AI-Assisted Custom Column Creation (Age Group) Using AI assistance (Power Query Copilot / M formula logic), a custom conditional column named Age Group was engineered from the raw Age field to enable demographic segmentation on the dashboard:

if [Age] < 18 then "Under 18"
else if [Age] >= 18 and [Age] <= 25 then "18-25"
else if [Age] >= 26 and [Age] <= 35 then "26-35"
else if [Age] >= 36 and [Age] <= 45 then "36-45"
else if [Age] >= 46 and [Age] <= 60 then "46-60"
else "60+"

6. Data Modeling (Star Schema & Relationships) Once the cleaned tables were loaded into the Power BI data model, a structured relational model was configured: • Star Schema Architecture: Centered the transactional data (orders) as the core Fact table, surrounding it with lookup/dimension tables (customers, products, payments).
• Relationship Integrity: Established active 1-to-Many (1:*) relationships connecting primary key fields (e.g., customers[CustomerID] to orders[CustomerID] and products[ProductID] to orders[ProductID]).
• Cross-Filtering & Performance: Ensured single-direction filtering across dimensions to optimize DAX evaluation speed and eliminate circular model dependencies.

7. DAX Measures for Executive KPIs To power the summary card visuals at the top of the dashboard, explicit DAX (Data Analysis Expressions) measures were authored rather than relying on implicit aggregations. This ensured dynamic responsiveness across all slicers and drill-downs: • Total Revenue (£3.50M): Summed transaction values across orders.

Total Revenue = SUM(Orders[Revenue])

• Total Customers (10K): Counted unique customer identifiers. Code snippet

  Total Customers = DISTINCTCOUNT(Customers[CustomerID])
  

• Total Orders (50K): Evaluated overall transactional order volume. Code snippet

  Total Orders = COUNT(Orders[OrderID])
  

• Total Quantity (94K): Summed item units sold across all categories. Code snippet

  Total Quantity = SUM(Orders[Quantity])
  

• Avg. Discount (7.48%): Calculated average promotional discount applied. Code snippet

  Avg. Discount = AVERAGE(Orders[Discount])
  

• Avg. Unit Price (£61.40): Calculated mean selling price across product catalogs. Code snippet

  Avg. Unit Price = AVERAGE(Products[UnitPrice])