End-to-End Data Cleaning & Preprocessing using Power Query

Executive Overview:

Executed a professional Data Preprocessing & Cleaning Workflow using Power Query (Excel) to transform an unstructured, messy transactional sales dataset into a clean, standardized, and business-ready analytical model.

Key Data Quality Challenges Handled:

Standardization & Casing: Normalized inconsistent text formats (UPPERCASE, lowercase, and Mixed Casing) across Customer Names, Categories, Payment Methods, and Status columns.

Data Cleansing & Trimming: Removed leading/trailing whitespace, null placeholders (N/A, unknown), and duplicate records.

Geographic Consistency: Mapped and standardized country naming variations (e.g., Egypt / EG / EGYPT, USA / United States).

Date Parsing: Standardized disparate date formats (ISO, DD/MM/YYYY, MM/DD/YYYY, text dates) into a unified Date data type.

Financial & Currency Formatting: Cleared text prefixes (e.g., $ signs) from numeric columns (UnitPrice) and imputed missing TotalAmount calculations (Quantity × UnitPrice).

Data Validation: Resolved phone number formatting irregularities and filtered out invalid negative order quantities.

Technical Stack & Skills:

Microsoft Excel & Power Query (ETL): Data extraction, transformation, conditional columns, type casting, and custom M-code logic.

Data Quality & Integrity: Automated repetitive cleaning steps to ensure reproducible data pipelines.