📦 Module 7 Dataset Documentation: Data Cleaning & Enterprise Ingestion

🗂️ Workbook Tab Directory

🌐 Dataset Provenance & The PwC Switzerland Simulation Suite

Both datasets ingested into Module_7_Demo.xlsx are industry-standard benchmark assets with validated public mirrors and authentic enterprise provenance:

1. The PwC Switzerland Power BI Virtual Case Experience Suite (Forage)

The PWC Source Data tab in Module_7_Demo.xlsx represents Dataset 01 of the acclaimed PwC Switzerland – Power BI Virtual Case Experience hosted on Forage. In corporate analytics, this simulation is celebrated for delivering real-world, messy operational data across three distinct corporate departments:

flowchart TD
    PWC["PwC Switzerland Power BI Virtual Case Experience\n(Forage Enterprise Simulation)"]
    
    T1["Task 1: Call Centre Trends\n(01 Call-Center-Dataset.xlsx)\n• 5,000 Inbound Call Records\n• 8 Service Agents | Q1 2021\n• Ingested in Module 7 Demo"]
    T2["Task 2: Customer Retention\n(02 Churn-Dataset.xlsx)\n• 7,043 Telco Customer Records\n• 23 Churn & Service Attributes\n• Churn Risk & Contract Elasticity"]
    T3["Task 3: Diversity & Inclusion\n(03 Diversity-Inclusion-Dataset.xlsx)\n• 500 Corporate Employee Records\n• Executive Hierarchy & Promotions\n• Gender Parity & Turnover Scorecards"]
    
    PWC --> T1
    PWC --> T2
    PWC --> T3
    
    style PWC fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
    style T1 fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style T2 fill:#fff3e0,stroke:#ef6c00,stroke-width:2px
    style T3 fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px

📦 The Complete Tripartite Dataset Suite

🧩 Detailed Task Breakdown & Official DAX Models

Task 1: The Call Centre Trends

• Client Sponsor: Claire (Call Centre Manager at an international telecom company).
• Executive Mandate: Create an executive Power BI dashboard reflecting all relevant Key Performance Indicators (KPIs) and operational metrics in the dataset to better understand customer trends and agent performance.
• 2-Page Solution Architecture:
  • The KPI Dashboard: High-level overview of overall satisfaction (CSAT), answer vs abandonment rates, call arrival by time of day, and average speed of answer.
  • The Detail Page: Topic-level drill-down and the Agent's Performance Quadrant: Average Handle Time (Talk Duration) vs Calls Answered.
• Official DAX Measures:
  DAX Copy

  Total Call = DISTINCTCOUNT(CallData[Call Id])
  No of Answered Call = CALCULATE(COUNT(CallData[Call Id]), 'CallData'[Answered (Y/N)]="Y")
  No of Abandoned Call = CALCULATE(COUNT(CallData[Call Id]), 'CallData'[Answered (Y/N)]="N")
  Abandoned Rate = DIVIDE([No of Abandoned Call], DISTINCTCOUNT(CallData[Call Id]))
  Answ to Abandoned Rate = DIVIDE([No of Answered Call], [No of Abandoned Call])
  Avg Speed of Answer = DIVIDE(SUM(CallData[Speed of answer in seconds]), [No of Answered Call])
  Resolved Call = CALCULATE(COUNT(CallData[Resolved]), AND(CallData[Answered (Y/N)]="Y", CallData[Resolved]="Y"))
  Call Resolution Rate (%) = DIVIDE([Resolved Call], [No of Answered Call])
  Duration per Answered Call = DIVIDE(CALCULATE(SUM(CallData[CallDuration]), CallData[CallDuration]>0), [No of Answered Call])
  Satisfaction Score = DIVIDE(CALCULATE(SUM(CallData[Satisfaction rating]), CallData[Answered (Y/N)]="Y"), [No of Answered Call])
  

• Live Power BI Report: PwC Call Centre Dashboard (Interactive)

Task 2: The Customer Retention

• Client Sponsor: Retentions Manager (Telecommunications Industry).
• Executive Mandate: Customers in telecom are hard-earned; the retention department historically contacted customers after they terminated their contracts (reactionary). They commissioned a predictive retention dashboard to identify customers at risk before cancellation.
• Key DAX Measures:
  DAX Copy

  # Customer = DISTINCTCOUNT(ChurnDataset[CustID])
  #Churn = CALCULATE(COUNT(ChurnDataset[Churn]), ChurnDataset[Churn]="Yes")
  Churn Rate = DIVIDE('Measures Table'[#Churn], [# Customer])
  # Dependents = CALCULATE(DISTINCTCOUNT(ChurnDataset[CustID]), ChurnDataset[Dependents]="Yes")
  # DeviceProtection = CALCULATE(DISTINCTCOUNT(ChurnDataset[CustID]), ChurnDataset[DeviceProtection]="Yes")
  # TechSupport = CALCULATE(DISTINCTCOUNT(ChurnDataset[CustID]), ChurnDataset[TechSupport]="Yes")
  # Paperless = CALCULATE(DISTINCTCOUNT(ChurnDataset[CustID]), ChurnDataset[PaperlessBilling]="Yes")
  

• Live Power BI Report: PwC Customer Churn & Retention Dashboard

Task 3: Diversity and Inclusion

• Client Sponsor: Human Resources Leadership (Pharma Group AG).
• Executive Mandate: Diagnose root causes behind the slow progress in achieving gender balance at the executive management level through hiring, promotion, and turnover analytics.
• Key DAX Measures:
  DAX Copy

  #Men = CALCULATE(DISTINCTCOUNT('Pharma Group AG'[Employee ID]), 'Pharma Group AG'[Gender]="Male")
  #Women = CALCULATE(DISTINCTCOUNT('Pharma Group AG'[Employee ID]), 'Pharma Group AG'[Gender]="Female")
  #Leaver FY20 = CALCULATE(DISTINCTCOUNT('Pharma Group AG'[Employee ID]), 'Pharma Group AG'[FY20 leaver?]="Yes")
  #Promoted Employee = CALCULATE([Total Employee EO FY20], 'Pharma Group AG'[Promotion in FY21?]="Yes")
  Turnover Rate = DIVIDE([#Leaver FY20], 0.5*([Total Employee AB FY20]+[Total Employee EO FY20]))
  Promotion Rate = DIVIDE([#Promoted Employee], [Total Employee EO FY20])
  Promoted Men Rate = DIVIDE([#Promoted Men], [#Promoted Employee])
  Promoted Women Rate = DIVIDE([#Promoted Women], [#Promoted Employee])
  

🏛️ Provenance, Documentation & Public Mirrors

• Canonical Simulation Write-up & Documentation: triwgani.github.io/pwc_digital.transformation — Independent project documentation comprehensively mapping all three simulation tasks and dataset schemas.
• Complete Simulation Suite GitHub Repository: Boomslang-Maverick/PWC-Forage-Power-BI-Virtual-Experience — Preserves all three original .xlsx datasets alongside their respective PwC task briefs and business scenario guidelines.
• Task 1 Single-File Mirror: globalsmile/Call-Center-Analysis — Dedicated public mirror of Dataset 01 matching our course raw file byte-for-byte.
• Official Forage Simulation Enrollment: PwC Switzerland Power BI Virtual Case Experience.
• Course Raw Master File: 09_Source_Materials/Module 9/13/PWC Dataset.xlsx (Sheet: Source Data ).

2. Hotel Reservations Hospitality Benchmark Dataset

• Canonical Origin: Hotel Reservations Classification Dataset (CC0 Public Domain) by Ahsan on Kaggle.
• Context: 36,275 real hospitality booking records spanning 2017–2018 for cancellation modeling and ADR revenue analysis.
• Kaggle Primary Repository: Kaggle: Hotel Reservations Dataset
• Hugging Face Mirror: Hugging Face Parquet/CSV Mirror
• Local Course Asset: D:\courses\Data Analysis 26-27\Hotel Reservations.csv.

3. Microsoft SQL Server AdventureWorks2022 Enterprise Benchmark & Ingestion Lineage

The People (19,972 rows) and Product (504 rows) tables in Module_7_Demo.xlsx are direct relational extracts derived from Microsoft's canonical enterprise OLTP benchmark database: AdventureWorks2022.

🏛️ Background & Provenance of AdventureWorks

AdventureWorks is Microsoft's premier sample database simulating an international bicycle manufacturing corporation (Adventure Works Cycles). It demonstrates normalized enterprise relational architecture (3NF) encompassing Human Resources, Purchasing, Sales, and Production schemas.

💾 Local Database Backup & Restoration Specification

In our local course environment, the complete database archive is stored as a compressed SQL Server backup file:

• Local Backup Path: D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak
• Target SQL Server Instance: localhost / . / Sohila (Microsoft SQL Server 2022 Developer Edition v16.0.1200.5)
• Instance Default Data Directory: D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\

flowchart LR
    BAK[("AdventureWorks2022.bak\n(D:\\SQL Server\\...\\Backup\\)")]
    
    subgraph RESTORE ["SQL Server 2022 Engine (Localhost / .)"]
        REST_CMD["RESTORE DATABASE AdventureWorks2022\nWITH MOVE ... REPLACE"]
    end
    
    subgraph DATA_FILES ["MSSQL DATA Directory"]
        MDF[("AdventureWorks2022.mdf\n(Primary Data File: 215 MB)")]
        LDF[("AdventureWorks2022_log.ldf\n(Transaction Log: 75 MB)")]
    end
    
    BAK --> REST_CMD
    REST_CMD --> MDF
    REST_CMD --> LDF

    style BAK fill:#fff3e0,stroke:#ef6c00,stroke-width:2px
    style RESTORE fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style DATA_FILES fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px

Step 1: Inspect Logical File Names via T-SQL

Executing RESTORE FILELISTONLY reveals the internal logical data and transaction log file names:

RESTORE FILELISTONLY 
FROM DISK = N'D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak';
GO

Verified Logical File Metadata:

Step 2: Production T-SQL Restoration Script

Execute the following script in SQL Server Management Studio (SSMS) or Azure Data Studio to restore the database to your local instance:

USE master;
GO

-- 1. Disconnect any active connections to prevent restore locks
IF DB_ID('AdventureWorks2022') IS NOT NULL
BEGIN
    ALTER DATABASE AdventureWorks2022 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO

-- 2. Restore database with file relocation to local MSSQL16 DATA directory
RESTORE DATABASE AdventureWorks2022
FROM DISK = N'D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak'
WITH 
    MOVE N'AdventureWorks2022' TO N'D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022.mdf',
    MOVE N'AdventureWorks2022_log' TO N'D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022_log.ldf',
    REPLACE,
    STATS = 10;
GO

-- 3. Return database to standard multi-user mode
ALTER DATABASE AdventureWorks2022 SET MULTI_USER;
GO

Step 3: Instant PowerShell / Terminal Execution via sqlcmd

For fast headless execution:

sqlcmd -S . -E -Q "RESTORE DATABASE AdventureWorks2022 FROM DISK = N'D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak' WITH MOVE N'AdventureWorks2022' TO N'D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022.mdf', MOVE N'AdventureWorks2022_log' TO N'D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022_log.ldf', REPLACE, STATS = 10;"

Step 4: Graphical Restoration via SSMS

1. Open SQL Server Management Studio (SSMS) and connect to . (or localhost).
2. In Object Explorer, right-click Databases $\to$ select Restore Database....
3. Under Source, select Device $\to$ click the ellipsis ... $\to$ click Add.
4. Browse to D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak and click OK.
5. In the left navigation pane, click Files $\to$ verify or check Relocate all files to folder (ensuring paths target D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\).
6. In the left pane, click Options $\to$ check Overwrite the existing database (WITH REPLACE).
7. Click OK. Upon successful completion, SSMS will display: "Database 'AdventureWorks2022' restored successfully."

🔗 The Course Extraction Pipeline: How Quries.sql Generated Course Files

The course script Quries.sql (09_Source_Materials/Module 7/2-Importing Data/Quries.sql) bridges SQL Server tables directly into the XML, JSON, and Excel files analyzed in Module 7:

flowchart TD
    AW[("AdventureWorks2022\n(SQL Server 2022)")]
    
    subgraph EXPORT ["T-SQL Extraction via Quries.sql"]
        Q1["Query 1: FOR XML PATH('Product')\nFROM Production.Product"]
        Q2["Query 2: FOR JSON PATH, ROOT('People')\nFROM Person.Person JOIN Email + Phone"]
        Q3["Query 3: Native Pushdown Query\nFROM Person.Person JOIN EmailAddress\nWHERE EmailAddress LIKE 'a%'"]
    end
    
    subgraph ARTIFACTS ["Generated Course Artifacts"]
        F1["XML_F52E2B61-18A1-11d1-B105-00805F49916B1.xml\n(504 Products)"]
        F2["JSON_F52E2B61-18A1-11d1-B105-00805F49916B3.json\n(19,972 Contact Records)"]
        F3["Live Power Query Table\n(1,140 Rows Filtered on Server)"]
    end
    
    subgraph EXCEL ["Module_7_Demo.xlsx"]
        T1["Worksheet: Product\n(504 rows, 4 columns)"]
        T2["Worksheet: People\n(19,972 rows, 5 columns)"]
        T3["Pushdown SQL Feed\n(Direct Server Ingestion)"]
    end
    
    AW --> Q1 --> F1 --> T1
    AW --> Q2 --> F2 --> T2
    AW --> Q3 --> F3 --> T3

    style AW fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style EXPORT fill:#fff8e1,stroke:#f57f17,stroke-width:2px
    style ARTIFACTS fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
    style EXCEL fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px

1. Product Table Lineage (XML Extraction):

   • T-SQL Source Query:
     SQL Copy

     SELECT ProductID, Name, ProductNumber, ListPrice
     FROM Production.Product
     FOR XML PATH('Product'), ROOT('Products');
     

   • Generated Artifact: XML_F52E2B61-18A1-11d1-B105-00805F49916B1.xml ($504$ items).
   • Power Query Ingestion: Parsed via Xml.Tables(File.Contents(...)), navigated to nested Product nodes, typed strictly into ProductID (Int64.Type), Name (type text), ProductNumber (type text), and ListPrice (type number).
   • Lineage Verification: Querying SELECT COUNT(*) FROM Production.Product; in AdventureWorks2022 returns 504 rows—a 100% exact match.

2. People Table Lineage (JSON Extraction):

   • T-SQL Source Query:
     SQL Copy

     SELECT 
         p.BusinessEntityID AS Id,
         p.FirstName,
         p.LastName,
         e.EmailAddress,
         ph.PhoneNumber
     FROM Person.Person p
     LEFT JOIN Person.EmailAddress e ON p.BusinessEntityID = e.BusinessEntityID
     LEFT JOIN Person.PersonPhone ph ON p.BusinessEntityID = ph.BusinessEntityID
     FOR JSON PATH, ROOT('People');
     

   • Generated Artifact: JSON_F52E2B61-18A1-11d1-B105-00805F49916B3.json ($19,972$ records).
   • Power Query Ingestion: Parsed via Json.Document(File.Contents(...)), converted People array into table lines, expanded 5 record keys: Id, FirstName, LastName, EmailAddress, PhoneNumber.
   • Lineage Verification: Querying SELECT COUNT(*) FROM Person.Person; in AdventureWorks2022 returns 19,972 rows—a 100% exact match.

3. Direct Database Pushdown Connection (Channel 7):

   • Rather than extracting intermediate flat files, analysts connect Excel directly to SQL Server:
     • Path: Data > Get Data > From Database > From SQL Server Database.
     • Server: . or localhost
     • Database: AdventureWorks2022
     • SQL Statement:
       SQL Copy

       SELECT 
           p.BusinessEntityID AS Id,
           p.FirstName,
           p.LastName,
           e.EmailAddress
       FROM Person.Person p
       JOIN Person.EmailAddress e ON p.BusinessEntityID = e.BusinessEntityID
       WHERE e.EmailAddress LIKE 'a%';
       

   • Pushdown Benefit: The SQL Server query optimizer executes the join and filter in milliseconds on indexed columns, returning only the 1,140 matching records to Excel, eliminating network overhead and client-side processing bottlenecks.

🧠 Connecting Excel to SQL & Relational Databases

Mastering the connection between Excel and SQL Server bridges the gap between spreadsheet modeling and enterprise data engineering:

4. Microsoft SQL Server Northwind & Pubs Sample Databases (Classic Relational Benchmarks)

Beyond AdventureWorks, Microsoft's classic sample databases—Northwind and pubs—remain the world's most widely recognized educational relational datasets. They provide compact, highly interpretable relational schemas designed specifically for learning SQL queries, stored procedures, views, and data warehousing dimensional modeling.

🏛️ Canonical Origin & Microsoft Source Repository

Microsoft maintains the original SQL Server creation and data-loading scripts in its official public repository:

• Official GitHub Repository: microsoft/sql-server-samples (Northwind & pubs)
• Microsoft Learn Documentation: Downloading Sample Databases (Northwind & pubs)
• Local Course Scripts:
  • D:\courses\Data Analysis 26-27\instnwnd.sql (Northwind database creation + data-loading script, 1,049,720 bytes)
  • D:\courses\Data Analysis 26-27\instpubs.sql (pubs publishing database creation + data-loading script, 125,718 bytes)

📦 The Northwind Database Hierarchy & Object Catalog

Northwind represents an international specialty food export-import business (Northwind Traders). It models sales transactions, catalog management, customer accounts, and employee territories:

flowchart TD
    subgraph NORTHWIND ["Northwind Database Architecture"]
        direction TB
        
        subgraph TABLES ["Base Tables (13 Tables)"]
            T1["Customers (91 records)"]
            T2["Orders (830 transactions)"]
            T3["Order Details (2,155 line items)"]
            T4["Products (77 catalog items)"]
            T5["Categories (8 food groups)"]
            T6["Suppliers (29 global vendors)"]
            T7["Employees (9 sales staff)"]
            T8["Shippers (3 freight carriers)"]
            T9["Region (4 territories) & Territories (53)"]
            T10["EmployeeTerritories (49 mapping links)"]
            T11["CustomerDemographics & CustomerCustomerDemo"]
        end

        subgraph VIEWS ["Analytical Reporting Views (16 Views)"]
            V1["Summary of Sales by Quarter"]
            V2["Summary of Sales by Year"]
            V3["Sales by Category"]
            V4["Sales Totals by Amount"]
            V5["Invoices & Order Details Extended"]
            V6["Product Sales for 1997"]
        end

        TABLES --> VIEWS
    end

    style NORTHWIND fill:#fbfbfb,stroke:#37474f,stroke-width:2px
    style TABLES fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style VIEWS fill:#fff3e0,stroke:#ef6c00,stroke-width:2px

Northwind Database Object Tree:
Northwind
├── 📁 Base Tables
│   ├── Categories (8 rows)
│   ├── CustomerCustomerDemo (0 rows)
│   ├── CustomerDemographics (0 rows)
│   ├── Customers (91 rows)
│   ├── Employees (9 rows)
│   ├── EmployeeTerritories (49 rows)
│   ├── Order Details (2,155 rows)
│   ├── Orders (830 rows)
│   ├── Products (77 rows)
│   ├── Region (4 rows)
│   ├── Shippers (3 rows)
│   ├── Suppliers (29 rows)
│   └── Territories (53 rows)
└── 📁 Core Saved Views / Reporting Queries
    ├── Sales by Category
    ├── Sales Totals by Amount
    ├── Summary of Sales by Quarter
    ├── Summary of Sales by Year
    ├── Alphabetical list of products
    ├── Category Sales for 1997
    ├── Current Product List
    ├── Customer and Suppliers by City
    ├── Invoices
    ├── Order Details Extended
    ├── Order Subtotals
    ├── Orders Qry
    ├── Product Sales for 1997
    ├── Products Above Average Price
    ├── Products by Category
    └── Quarterly Orders

🛠️ Installation & Recreation Walkthroughs

1. Command-Line Execution via sqlcmd (Recommended)

You can create and load both databases in a single automated terminal command:

# 1. Create empty database and run Northwind script
sqlcmd -S . -E -d master -Q "IF DB_ID('Northwind') IS NULL CREATE DATABASE Northwind;"
sqlcmd -S . -E -d Northwind -i "D:\courses\Data Analysis 26-27\instnwnd.sql"

# 2. Run pubs script (automatically creates and populates pubs)
sqlcmd -S . -E -i "D:\courses\Data Analysis 26-27\instpubs.sql"

2. SQL Server Management Studio (SSMS) Execution

1. Open SSMS and connect to your local server (. or localhost).
2. Open a query window on master and create the database:
   SQL Copy

   IF DB_ID('Northwind') IS NULL CREATE DATABASE Northwind;
   GO
   

3. Open D:\courses\Data Analysis 26-27\instnwnd.sql in SSMS (File > Open > File...).
4. Verify the database selector dropdown is set to Northwind $\to$ press F5.
5. Open D:\courses\Data Analysis 26-27\instpubs.sql in SSMS $\to$ press F5.

📊 Connecting Northwind to Excel & Power Pivot Star Schemas

Northwind provides the cleanest possible illustration of the difference between flat spreadsheets and relational dimensional models:

5. Egyptian Demographic Statistics Suite (Arabic Wikipedia Web Extraction)

The workbook Module_7_Demo.xlsx incorporates live, automated web scraping connecting Excel directly to Arabic Wikipedia:

• Canonical Web Endpoint: Wikipedia: التركيبة السكانية في مصر (Demographics of Egypt)
• Data Origin & Governance: Demographic censuses, vital statistics, and regional projections compiled by Egypt's Central Agency for Public Mobilization and Statistics (CAPMAS - الجهاز المركزي للتعبئة العامة والإحصاء).
• Ingestion Technology: Power Query's headless web rendering engine (Web.BrowserContents) parsed into typed tabular datasets via CSS selectors in Html.Table.

flowchart TD
    WIKI["Arabic Wikipedia Article\n(التركيبة السكانية في مصر)"] --> WB_GET["Power Query Engine:\nWeb.BrowserContents(URL)"]
    WB_GET --> HTML_TAB["Html.Table(Source, CSS Selectors)\nParses HTML DOM & wikitable classes"]
    
    HTML_TAB --> T15["Table 15: Vital Statistics\n(Year, Births, Deaths, Natural Change, Crude Rates)"]
    HTML_TAB --> T16["Table 16: Fertility Targets\n(Urban vs Rural Targets & Crude Birth Rates)"]
    HTML_TAB --> T17["Table 17: Life Expectancy\n(Historical Trajectory & Projections)"]
    HTML_TAB --> T18["Table 18: 2024 Governorate Split\n(Urban/Rural Ratios & Headcounts)"]
    HTML_TAB --> T19["Table 19: Area & Population Density\n(Total Area, Inhabited %, Pop Density)"]

    style WIKI fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style WB_GET fill:#fff3e0,stroke:#ef6c00,stroke-width:2px
    style HTML_TAB fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
    style T15 fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px
    style T16 fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px
    style T17 fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px
    style T18 fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px
    style T19 fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px

📦 The 5 Web-Scraped Ingestion Feeds

1. Table 15 — Vital Statistics of Egypt: Multi-decade time series tracking national population, total live births, deaths, natural population growth, crude birth rate per 1,000, crude death rate per 1,000, net migration rate, and total fertility rate.
2. Table 16 — Birth & Fertility Rate Projections: Compares total national crude birth rates and targets against urban and rural breakdown benchmarks (معدل الخصوبة الفعلي في الريف / الحضر).
3. Table 17 — Life Expectancy Historical Progression: Life expectancy at birth (متوسط العمر المتوقع بالسنوات) tracked from mid-20th century to present.
4. Table 18 — 2024 Population by Governorate: Detailed spatial breakdown across Egypt's 27 governorates, reporting urban population ratio (نسبة الحضر), 2024 census headcount, total rural population, and total urban population.
5. Table 19 — Governorate Land Area & Inhabited Density: Geographic territory ($km^2$), percentage of inhabited land area relative to total area, total population in thousands, and true demographic density concentrated within inhabited valleys and oases.

As executed live in Module_7_Demo.xlsx, data flows through the classic three-tier enterprise ETL cycle:

flowchart TD
    subgraph E ["E -: Extract Data -->> (Done)"]
        direction TB
        E1[("Hotel Reservations.csv\n(Local CSV File)")] --> PQ1["Power Query M:\nCsv.Document(File.Contents(...))"]
        E2[("PWC Dataset.xlsx\n(External Workbook)")] --> PQ2["Power Query M:\nExcel.Workbook(File.Contents(...))"]
    end

    subgraph T ["T -: Transform Data -->> (Active Practice)"]
        direction TB
        T1["Table.PromoteHeaders\n(Promote first row to column headers)"]
        T2["Table.TransformColumnTypes\n(Cast IDs to Text, Metrics to Numbers, Dates to Date)"]
        T3["Data Sanitization & Auditing\n• Strip trailing blank ghost columns (Column11–14)\n• Differentiate valid operational nulls (Abandoned calls)\n• Identify price & lead-time outliers"]
    end

    subgraph L ["L -: Load Data -->> (Done)"]
        direction TB
        L1["Table: Hotel_Reservations\n(Sheet: Hotel Reservations, 36,275 rows)"]
        L2["Table: ExternalData_2\n(Sheet: PWC Source Data, 5,000 rows)"]
    end

    E ==> T
    T ==> L

    style E fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style T fill:#fff3e0,stroke:#ef6c00,stroke-width:2px
    style L fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px

36  ETL:
37
38  E -: Extract Data   -->> (Done)
39                      |
40  T -: Transform Data -->> (Active Practice)
41  L -: Load Data      -->> (Done)

🏗️ Modern Excel Analytics Ecosystem Architecture

flowchart TD
    subgraph WB ["Microsoft Excel Workbook Ecosystem"]
        direction TB
        PQ["Power Query\n-->> Cleaning, Transformation & Modelling"]
        PP["Power Pivot\n-->> Data Model & Relationships (Star Schema)"]
        PT["Pivot Tables\n-->> Summary, Aggregations & Executive KPIs"]
        
        PQ ==>|"Clean Tabular Model / Load"| PP
        PP ==>|"Relationships & DAX Measures"| PT
    end

    style WB fill:#fafafa,stroke:#37474f,stroke-width:2px
    style PQ fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style PP fill:#fff3e0,stroke:#ef6c00,stroke-width:2px
    style PT fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px

Excel Workbook
Pivot Tables    -->> Summary
Power Query     -->> Cleaning and transformation and modelling
Power Pivot     -->> Data Model -- Relationships

💻 Live Power Query M Script Specifications

The queries inside Module_7_Demo.xlsx are driven by native Power Query M code stored in the workbook's Data Mashup package (customXml/item1.xml):

1. Hotel Reservations Extraction & Type Casting

shared #"Hotel Reservations" = let
    // Step 1: Extract CSV payload using comma delimiter
    Source = Csv.Document(
        File.Contents("D:\courses\Data Analysis 26-27\Hotel Reservations.csv"),
        [Delimiter=",", Columns=19, QuoteStyle=QuoteStyle.None]
    ),
    // Step 2: Promote first row of text to column headers
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    // Step 3: Transform column types into strict types
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"Booking_ID", type text},
        {"no_of_adults", Int64.Type},
        {"no_of_children", Int64.Type},
        {"no_of_weekend_nights", Int64.Type},
        {"no_of_week_nights", Int64.Type},
        {"type_of_meal_plan", type text},
        {"required_car_parking_space", Int64.Type},
        {"room_type_reserved", type text},
        {"lead_time", Int64.Type},
        {"arrival_year", Int64.Type},
        {"arrival_month", Int64.Type},
        {"arrival_date", Int64.Type},
        {"market_segment_type", type text},
        {"repeated_guest", Int64.Type},
        {"no_of_previous_cancellations", Int64.Type},
        {"no_of_previous_bookings_not_canceled", Int64.Type},
        {"avg_price_per_room", type number},
        {"no_of_special_requests", Int64.Type},
        {"booking_status", type text}
    })
in
    #"Changed Type";

2. PWC Call Center Extraction (Original Forage Dataset: 01 Call-Center-Dataset.xlsx)

shared #"Source Data" = let
    // Step 1: Ingest authentic Forage Excel workbook
    Source = Excel.Workbook(
        File.Contents("D:\courses\Data Analysis 26-27\01 Call-Center-Dataset.xlsx"), 
        null, 
        true
    ),
    // Step 2: Navigate to Sheet1 data payload
    Sheet1_Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
    // Step 3: Promote first row of headers
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    // Step 4: Enforce strict typed schema across all 10 canonical operational fields
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"Call Id", type text}, 
        {"Agent", type text}, 
        {"Date", type date}, 
        {"Time", type datetime}, 
        {"Topic", type text}, 
        {"Answered (Y/N)", type text}, 
        {"Resolved", type text}, 
        {"Speed of answer in seconds", Int64.Type}, 
        {"AvgTalkDuration", type datetime}, 
        {"Satisfaction rating", Int64.Type}
    })
in
    #"Changed Type";

3. Retail E-Commerce Extraction (Sample_ Superstore.csv)

shared #"Sample_ Superstore" = let
    // Step 1: Extract CSV payload using UTF-8 encoding (65001)
    Source = Csv.Document(
        File.Contents("D:\courses\Data Analysis 26-27\Sample_ Superstore.csv"),
        [Delimiter=",", Columns=19, Encoding=65001, QuoteStyle=QuoteStyle.None]
    ),
    // Step 2: Promote first row of text to column headers
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    // Step 3: Enforce strict typed schema
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"Row ID", Int64.Type}, {"Order ID", type text}, {"Order Date", type text}, 
        {"Ship Date", type text}, {"Ship Mode", type text}, {"Customer ID", type text}, 
        {"Segment", type text}, {"Country", type text}, {"City", type text}, 
        {"State", type text}, {"Region", type text}, {"Product ID", type text}, 
        {"Category", type text}, {"Sub-Category", type text}, {"Product Name", type text}, 
        {"Sales", type number}, {"Quantity", Int64.Type}, {"Discount", type number}, 
        {"Profit", type number}
    })
in
    #"Changed Type";

4. Parts Catalog XML Extraction (XML_F52E2B61-18A1-11d1-B105-00805F49916B1.xml)

shared Product = let
    // Step 1: Parse single-root XML document into nested table structures
    Source = Xml.Tables(
        File.Contents("D:\courses\Data Analysis 26-27\7-Introducation to Data Fields (Excel)\09_Source_Materials\Module 7\2-Importing Data\XML_F52E2B61-18A1-11d1-B105-00805F49916B1.xml")
    ),
    // Step 2: Navigate to nested Product records table
    Table0 = Source{0}[Table],
    // Step 3: Enforce strict numeric and string types across all 504 items
    #"Changed Type" = Table.TransformColumnTypes(Table0,{
        {"ProductID", Int64.Type}, 
        {"Name", type text}, 
        {"ProductNumber", type text}, 
        {"ListPrice", type number}
    })
in
    #"Changed Type";

5. Personnel Register JSON Extraction (JSON_F52E2B61-18A1-11d1-B105-00805F49916B3.json)

shared People = let
    // Step 1: Read JSON document stream
    Source = Json.Document(
        File.Contents("D:\courses\Data Analysis 26-27\7-Introducation to Data Fields (Excel)\09_Source_Materials\Module 7\2-Importing Data\JSON_F52E2B61-18A1-11d1-B105-00805F49916B3.json")
    ),
    // Step 2: Extract nested People list object
    PeopleList = Source[People],
    // Step 3: Convert list of records into tabular format
    TableFromList = Table.FromList(PeopleList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // Step 4: Expand record keys into distinct columns (19,972 total rows)
    ExpandedColumn = Table.ExpandRecordColumn(
        TableFromList, 
        "Column1", 
        {"Id", "FirstName", "LastName", "EmailAddress", "PhoneNumber"}
    )
in
    ExpandedColumn;

6. SQL Server Pushdown Query (Query1 from AdventureWorks2022)

shared Query1 = let
    // Step 1: Transmit pushdown SQL query directly to local AdventureWorks2022 instance
    Source = Sql.Database("localhost", "AdventureWorks2022", [
        Query="SELECT #(lf)    p.BusinessEntityID, #(lf)    p.FirstName, #(lf)    p.LastName, #(lf)    e.EmailAddress#(lf)FROM Person.Person AS p#(lf)LEFT JOIN Person.EmailAddress AS e #(lf)    ON p.BusinessEntityID = e.BusinessEntityID#(lf)WHERE p.FirstName LIKE 'A%';"
    ])
in
    Source;

7. Arabic Wikipedia Egyptian Demographics Web Extraction (Table 15 & Table 18)

shared #"Table 15" = let
    // Step 1: Ingest rendered web page DOM from Arabic Wikipedia
    Source = Web.BrowserContents("https://ar.wikipedia.org/wiki/%D8%A7%D9%84%D8%AA%D8%B1%D9%83%D9%8A%D8%A8%D8%A9_%D8%A7%D9%84%D8%B3%D9%83%D8%A7%D9%86%D9%8A%D8%A9_%D9%81%D9%8A_%D9%85%D8%B5%D8%B1"),
    // Step 2: Extract target HTML table elements using CSS selector paths
    #"Extracted Table From Html" = Html.Table(Source, {
        {"Column1", "TABLE[id='mwAh4'] > * > TR > :nth-child(1)"}, 
        {"Column2", "TABLE[id='mwAh4'] > * > TR > :nth-child(2)"}, 
        {"Column3", "TABLE[id='mwAh4'] > * > TR > :nth-child(3)"}, 
        {"Column4", "TABLE[id='mwAh4'] > * > TR > :nth-child(4)"}, 
        {"Column5", "TABLE[id='mwAh4'] > * > TR > :nth-child(5)"}, 
        {"Column6", "TABLE[id='mwAh4'] > * > TR > :nth-child(6)"}, 
        {"Column7", "TABLE[id='mwAh4'] > * > TR > :nth-child(7)"}, 
        {"Column8", "TABLE[id='mwAh4'] > * > TR > :nth-child(8)"}, 
        {"Column9", "TABLE[id='mwAh4'] > * > TR > :nth-child(9)"}, 
        {"Column10", "TABLE[id='mwAh4'] > * > TR > :nth-child(10)"}
    }, [RowSelector="TABLE[id='mwAh4'] > * > TR"]),
    // Step 3: Promote first row of HTML text to Arabic headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    // Step 4: Cast demographic time series into integers and decimals
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"السنة", Int64.Type}, 
        {"عدد السكان", type text}, 
        {"المواليد", type text}, 
        {"الوفيات", type text}, 
        {"التغير الطبيعي", type text}, 
        {"معدل المواليد الخام (لكل 1000)", type number}, 
        {"معدل الوفيات الخام (لكل 1000)", type number}, 
        {"التغير الطبيعي (لكل 1000)", type number}, 
        {"معدل الهجرة الخام (لكل 1000)", type number}, 
        {"معدل الخطوبة الكلي", type text}
    })
in
    #"Changed Type";

📋 Comprehensive Field Catalog

Table 1: Hotel_Reservations ($36,275 \times 19$)

Table 2: Sheet1 / PwC Call Center ($5,000 \times 10$)

Table 3: Sample__Superstore ($9,994 \times 19$)

Table 4: People ($19,972 \times 5$)

Table 5: Product ($504 \times 4$)

Table 6: Query1 / AdventureWorks SQL Pushdown ($2,014 \times 4$)

Table 7: Table 15 / Egypt Vital Statistics ($90 \times 10$)

Table 8: Table 18 / 2024 Population by Governorate ($27 \times 5$)

🎯 Hands-on Transformation Exercises in this Demo

1. Removing Irrelevant / Ghost Columns (Table.RemoveColumns)

When importing from older Excel files, phantom formatting in blank cells creates empty columns (Column11 to Column14).

• Power Query Action: Select Column11 through Column14 $\rightarrow$ Right-click $\rightarrow$ Remove Columns.
• Resulting M Code:
  POWERQUERY Copy

  #"Removed Columns" = Table.RemoveColumns(#"Changed Type", {"Column11", "Column12", "Column13", "Column14"})
  

2. Auditing Missing Values (The 946 Nulls Dilemma)

• In PWC Source Data, column Speed of answer in seconds has 946 null values.
• Rule: Never delete these rows! They correspond 100% to Answered (Y/N) = "N". Deleting them would falsely inflate the call resolution rate from 81.1% to 100%.

3. Date Engineering from Discrete Components

In Hotel Reservations, arrival is split into 3 columns: arrival_year, arrival_month, and arrival_date.

• Excel Formula:
  EXCEL Copy

  =DATE([@arrival_year], [@arrival_month], [@arrival_date])
  

• Power Query Custom Column:
  POWERQUERY Copy

  #Date([arrival_year], [arrival_month], [arrival_date])
  

Related Knowledge

• Notes:
  • 01 Data Quality Dimensions and Audit
  • 02 Data Cleaning Techniques in Excel
  • 03 Importing Data from Enterprise Sources
  • 04 Business Systems for Analysts
• Concepts: Data Cleaning, ETL Process, Power Query, Six Dimensions of Data Quality
• Demo Workbook: Module_7_Demo.xlsx