This repository contains a data transformation and analysis project performed using Power Query in Excel. The dataset consists of multiple related tables simulating a product and order management system for a company (similar to Northwind Traders). The goal was to clean, transform, and analyze the data to derive meaningful business insights.
To transform raw relational data into an analyzable format and answer business questions such as:
Total sales per category
Top-selling products
Sales trends over time
Customer order behavior
Microsoft Excel (with Power Query Editor)
Power Query M Language
Data Modeling (Relationships, Joins)
Data Cleaning & Transformation
- Data Loading and Inspection Loaded all sheets into Power Query.
Identified data types and cleaned column names.
- Data Cleaning Removed duplicates and null values.
Standardized text formats (e.g., CategoryName).
Corrected data types (e.g., dates, numbers).
- Data Transformation Joined Product with Category to get category names.
Joined OrderDetails with Orders and Product to enrich transaction data.
Calculated Sales column: Sales = [Quantity] * [UnitPrice] * (1 - [Discount])
- Data Enrichment Added calculated columns:
Year, Month, Quarter from OrderDate
Total Sales per order
CategorySales per transaction
- Data Modeling Established relationships between:
Orders and OrderDetails (on OrderID)
Products and OrderDetails (on ProductID)
Category and Products (on CategoryID)