Lesson 7.3: Ingesting Data from Enterprise Sources (The 9 Channels)

🎥 Video Chapter: Chapter 7 – Importing Data & Data Cleaning (3:54:03)

1. Visual Roadmap: The 9 Ingestion Channels

Modern Excel is not just a spreadsheet; it is an enterprise data orchestration client. Via the Power Query ETL engine (Data > Get Data), analysts connect directly to transactional systems, cloud lakes, and web services:

flowchart TD
    Root["Excel Data Ingestion\n(Data > Get Data)"]

    subgraph LocalFlat["Files & Local Objects"]
        C1["1. Table or Range\n(Current Workbook)"]
        C2["2. Excel File\n(.xlsx, .xlsb, .xlsm)"]
        C3["3. CSV / Text File\n(Delimited Flat Files)"]
        C4["4. Folder\n(Multi-file Consolidation)"]
    end

    subgraph SemiStructured["Semi-Structured Payloads"]
        C5["5. XML\n(Extensible Markup Language)"]
        C6["6. JSON\n(JavaScript Object Notation)"]
    end

    subgraph EnterpriseDB["Relational Databases"]
        C7["7. Database Direct\n(Table / View Navigator)"]
        C8["8. Special Query\n(Native SQL Pushdown)"]
    end

    subgraph WebCloud["Live Web & Cloud Services"]
        C9["9. Website\n(HTML Table Scraping)"]
        C10["10. REST API\n(Power Query M Web.Contents)"]
    end

    Root --> LocalFlat
    Root --> SemiStructured
    Root --> EnterpriseDB
    Root --> WebCloud

    style Root fill:#37474f,stroke:#263238,color:#fff,stroke-width:3px
    style LocalFlat fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px
    style SemiStructured fill:#fff3e0,stroke:#ef6c00,stroke-width:2px
    style EnterpriseDB fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style WebCloud fill:#f3e5f5,stroke:#6a1b9a,stroke-width:2px

2. Ingestion Channels 1 to 4: Files and Local Objects

Channel 1: From Table or Range (Local Workbook)

• Path: Data > From Sheet or Data > Get Data > From Other Sources > From Table/Range.
• Live Demo Implementation (Module_7_Demo.xlsx): Connects directly to the existing table Hotel_Reservations (36,275 rows) within the active workbook.
• Power Query M Script (Live from Module_7_Demo.xlsx):
  POWERQUERY Copy

  shared Hotel_Reservations = let
      Source = Excel.CurrentWorkbook(){[Name="Hotel_Reservations"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(Source,{
          {"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";
  

• Mechanism: Reads the local table via Excel.CurrentWorkbook(){[Name="..."]}[Content].
• Key Advantage: Dynamic range expansion. When new rows are typed beneath the Excel Table, Power Query automatically incorporates them upon Refresh (Alt + F5).

Channel 2: From External Excel File (.xlsx, .xlsm, .xlsb)

• Path: Data > Get Data > From File > From Excel Workbook.
• Live Demo Implementation (Module_7_Demo.xlsx): Ingesting the authentic PwC Forage dataset 01 Call-Center-Dataset.xlsx (Sheet: Sheet1) into table ExternalData_2 (5,000 call interaction records, 10 canonical columns).
• Mechanism: Reads metadata from an unopened external workbook. The Navigator dialog presents two object types:
  • 📋 Table Objects (Blue header icon): Represents formatted Excel Tables (Ctrl + T). Recommended! Tables automatically handle variable row counts without capturing empty trailing rows.
  • 📄 Sheet Objects (Sheet icon): Represents the raw grid. May contain blank header rows, titles, and empty cells outside the used range that require manual trimming.
• Power Query M Script (Authentic Forage Dataset):
  POWERQUERY Copy

  shared #"Source Data" = let
      Source = Excel.Workbook(File.Contents("D:\courses\Data Analysis 26-27\01 Call-Center-Dataset.xlsx"), null, true),
      Sheet1_Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
      #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
      #"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";
  

Channel 3: From CSV / Text File (Delimited Flat Files)

• Path: Data > Get Data > From File > From Text/CSV.
• Course Datasets:
  • Sample_ Superstore.csv (9,994 rows, Table: Sample__Superstore in Module_7_Demo.xlsx).
  • Hotel Reservations.csv (36,275 rows).
  • Supermarket data.csv (46.08 MB retail transaction log).
• Core Parameters:
  • File Origin (Encoding): Default is 65001: Unicode (UTF-8). If Arabic characters appear garbled (e.g. ??? or عميل), switch encoding to 1256: Arabic (Windows) or UTF-8 with BOM.
  • Delimiter: Auto-detected (Comma ,, Semicolon ;, Tab \t, Pipe |). European regional exports commonly use semicolons.
  • Data Type Detection: Based on first 200 rows or entire dataset.
• Power Query M Script (Live from Module_7_Demo.xlsx — Sample_ Superstore):
  POWERQUERY Copy

  shared #"Sample_ Superstore" = let
      Source = Csv.Document(
          File.Contents("D:\courses\Data Analysis 26-27\Sample_ Superstore.csv"),
          [Delimiter=",", Columns=19, Encoding=65001, QuoteStyle=QuoteStyle.None]
      ),
      #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
      #"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";
  

flowchart LR
    CSV["Hotel Reservations.csv\n(Raw Flat Text, 36,275 Rows)"] --> Connect["Data > From Text/CSV\nDelimiter: Comma (,)"]
    Connect --> Preview["Preview Dialog\n(Examines first 200 rows)"]
    Preview --> Transform["Transform Data\n(Promote Headers,\nCast Types in M)"]
    Transform --> Load["Close & Load\n(Table: Hotel_Reservations)"]

    style CSV fill:#ffebee,stroke:#c62828
    style Connect fill:#fff3e0,stroke:#ef6c00
    style Preview fill:#e1f5fe,stroke:#0288d1
    style Transform fill:#e8f5e9,stroke:#2e7d32
    style Load fill:#d1c4e9,stroke:#512da8

36  ETL:
37
38  E -: Extract Data   -->> (Done)
39                      |
40  T -: Transform Data -->> (In-Progress Practice in Module_7_Demo.xlsx)
41  L -: Load Data      -->> (Done)

flowchart TD
    subgraph WB ["Microsoft Excel Workbook Ecosystem"]
        direction TB
        PQ["Power Query\n-->> Cleaning, Transformation & Ingestion Modelling"]
        PP["Power Pivot\n-->> Data Model & Relationships (Star Schema)"]
        PT["Pivot Tables\n-->> Summary, Aggregations & Visual KPIs"]
        
        PQ ==>|"Clean Ingestion"| PP
        PP ==>|"Relationships & DAX"| 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

Channel 4: From Folder (Batch Multi-File Consolidation)

• Path: Data > Get Data > From File > From Folder.
• The Problem: A retail business generates 12 monthly sales files (Sales_Jan.xlsx, Sales_Feb.xlsx, ...). Manually copying and pasting each into a master file is error-prone and time-consuming.
• The Power Query Solution:
  1. Point Power Query to the folder path.
  2. Click Transform Data (Do NOT click Combine immediately!).
  3. Filter the Extension column to .csv or .xlsx (eliminates temporary files like ~$Sales_Jan.xlsx).
  4. Click the Combine Files icon (double down-arrow) in the Content column.
  5. Power Query builds an automated looping function, extracts rows from all 12 files, appends them vertically, and adds a Source.Name column tracking the origin file!

3. Ingestion Channels 5 & 6: Semi-Structured Formats (XML & JSON)

Enterprise web services, microservices, and modern database exports frequently deliver data in hierarchical, semi-structured schemas rather than flat tables.

Channel 5: From XML (Extensible Markup Language)

• Path: Data > Get Data > From File > From XML.
• Source Material Origin: Generated via SQL Server relational database query from Quries.sql:
  SQL Copy

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

• File Structure (XML_F52E2B61-18A1-11d1-B105-00805F49916B1.xml):
  XML Copy

  <Products>
    <Product>
      <ProductID>1</ProductID>
      <Name>Adjustable Race</Name>
      <ProductNumber>AR-5381</ProductNumber>
      <ListPrice>0.0000</ListPrice>
    </Product>
    <Product>
      <ProductID>2</ProductID>
      <Name>Bearing Ball</Name>
      <ProductNumber>BA-8327</ProductNumber>
      <ListPrice>0.0000</ListPrice>
    </Product>
  </Products>
  

• Power Query Parsing Workflow:
  • Power Query recognizes the <Products> root element.
  • Automatically identifies nested <Product> records.
  • Flattens hierarchical XML nodes into tabular columns: ProductID, Name, ProductNumber, and ListPrice.

Unable to connect
Details: "Xml processing failed. Either the input is invalid or it isn't supported. 
(Internal error: There are multiple root elements. Line 3027, position 2.)"

Channel 6: From JSON (JavaScript Object Notation)

• Path: Data > Get Data > From File > From JSON.
• Source Material Origin: Exported from SQL Server via Quries.sql:
  SQL Copy

  SELECT BusinessEntityID, FirstName, LastName
  FROM Person.Person
  FOR JSON PATH, ROOT('People');
  

• File Structure (JSON_F52E2B61-18A1-11d1-B105-00805F49916B3.json):
  JSON Copy

  {
    "People": [
      {
        "Id": 285,
        "FirstName": "Syed",
        "LastName": "Abbas",
        "EmailAddress": "syed0@adventure-works.com",
        "PhoneNumber": "926-555-0182"
      },
      {
        "Id": 293,
        "FirstName": "Catherine",
        "LastName": "Abel",
        "EmailAddress": "catherine0@adventure-works.com",
        "PhoneNumber": "747-555-0171"
      }
    ]
  }
  

• Power Query Record Expansion Workflow:
  1. In Power Query, JSON loads as a Record.
  2. Click on the yellow hyperlinked List adjacent to "People".
  3. Power Query converts the list into rows of Record objects.
  4. Click the Expand Column icon ([>]<[<]) in the upper-right corner of the column header.
  5. Select the target keys (Id, FirstName, LastName, EmailAddress, PhoneNumber).
  6. Uncheck "Use original column name as prefix".
  7. Instantly converts deeply nested JSON arrays into a pristine 19,972-row tabular dataset!

4. Ingestion Channel 7: Relational Databases (Direct vs Special Query)

Enterprise reporting relies on relational Database Management Systems (RDBMS) such as Microsoft SQL Server, Oracle, PostgreSQL, MySQL, and Microsoft Access.

flowchart TD
    subgraph ClientServer["Database Ingestion Architecture"]
        Excel["Microsoft Excel / Power Query"]
        SQLServer[("Enterprise SQL Server 2022\n(AdventureWorks2022 / Localhost)")]
    end

    subgraph DirectOption["Approach A: Database Direct (GUI)"]
        D1["Data > From SQL Server Database"] --> D2["Navigator Window"]
        D2 --> D3["Select Entire Table: Person.Person\n(19,972 Rows)"]
        D3 --> D4["Network transfers ALL 19,972 rows into Excel"]
        D4 --> D5["Excel filters where FirstName LIKE 'A%'\n(High Network & RAM Burden)"]
    end

    subgraph SpecialOption["Approach B: Special Query (Native SQL Pushdown)"]
        S1["Data > From SQL Server Database\n-> Advanced Options"] --> S2["Paste Custom SQL Statement\n(Pushdown Execution)"]
        S2 --> S3["Database Engine filters & joins locally\nTransfers ONLY 1,140 rows!"]
        S3 --> S4["Pristine lightweight extract in Excel\n(Optimal Performance)"]
    end

    Excel --> DirectOption
    Excel --> SpecialOption
    DirectOption -.-> SQLServer
    SpecialOption ==> SQLServer

    style DirectOption fill:#ffebee,stroke:#c62828,stroke-width:1px
    style SpecialOption fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px
    style SQLServer fill:#e3f2fd,stroke:#1565c0,stroke-width:2px

Approach A: Database (Standard Navigator Navigation)

• Path: Data > Get Data > From Database > From SQL Server Database.
• Inputs: Server Name (localhost or .), Database Name (AdventureWorks2022).
• Data Connectivity Mode: Import (caches data into Excel memory) vs DirectQuery (in Power BI).
• Execution: The user selects pre-existing tables or views from the Navigator tree view.

Approach B: Special Query (Native SQL Query Pushdown)

In enterprise environments, tables contain millions of rows. Pulling an entire table across the corporate network just to filter for a single region is a performance anti-pattern. Instead, analysts utilize Special Queries under Data > From SQL Server Database > Advanced Options > SQL statement.

Grounded Course Example from Quries.sql:

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

Why "Special Query" is Superior for Analysts:

1. Query Pushdown / Query Folding: The SQL Server engine executes the LEFT JOIN and WHERE filter across high-speed NVMe storage and dedicated server RAM.
2. Minimal Network Overhead: Instead of transferring 19,972 rows of Person and 19,972 rows of EmailAddress across the network, the database returns only the ~1,140 matching records starting with 'A'.
3. Column Projection: Excludes unneeded binary images, password hashes, and row GUIDs at the database level.

🛠️ Hands-on Guide: Restoring AdventureWorks2022.bak in SQL Server

To practice enterprise SQL ingestion locally, Microsoft provides the premier OLTP benchmark database: AdventureWorks2022.

• Backup File Location: D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak
• Target Instance: Microsoft SQL Server 2022 (Developer Edition) running on localhost (Service: MSSQLSERVER)
• Default Data Directory: D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\

Method 1: T-SQL Script (SSMS / Azure Data Studio / sqlcmd)

Run the following script to inspect the logical file names and restore the database to your local data folder:

-- Step 1: Inspect backup metadata and logical file names
RESTORE FILELISTONLY 
FROM DISK = N'D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak';
GO

-- Step 2: Restore database with MOVE to local instance DATA directory
USE master;
GO

-- Release any active locks if database already exists
IF DB_ID('AdventureWorks2022') IS NOT NULL
BEGIN
    ALTER DATABASE AdventureWorks2022 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO

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

-- Set back to multi-user mode
ALTER DATABASE AdventureWorks2022 SET MULTI_USER;
GO

-- Step 3: Verify restoration
USE AdventureWorks2022;
GO
SELECT COUNT(*) AS [Total Persons] FROM Person.Person;        -- Returns 19,972
SELECT COUNT(*) AS [Total Products] FROM Production.Product;    -- Returns 504
GO

Method 2: Graphical User Interface (SSMS)

1. Open SQL Server Management Studio (SSMS) and connect to Server Name: . or localhost.
2. In Object Explorer, right-click Databases $\rightarrow$ select Restore Database....
3. Under Source, select the Device radio button $\rightarrow$ click the ... button.
4. In the dialog, verify Backup media is set to File $\rightarrow$ click Add $\rightarrow$ navigate to D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak $\rightarrow$ click OK.
5. In the left navigation pane:
   • Click Files: Check the checkbox for Relocate all files to folder (or confirm files point to D:\SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\).
   • Click Options: Check Overwrite the existing database (WITH REPLACE).
6. Click OK. A message will confirm: "Database 'AdventureWorks2022' restored successfully."

Method 3: Instant PowerShell / Terminal Execution

You can restore the database directly from your command line using sqlcmd:

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;"

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

In addition to AdventureWorks, Microsoft created two foundational relational sample databases that have trained generations of data analysts, BI developers, and database administrators: Northwind and pubs.

🏛️ 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 schema + sample data, ~1.05 MB)
  • D:\courses\Data Analysis 26-27\instpubs.sql (pubs publishing database schema + sample data, ~125 KB)

📦 The Northwind Database Architecture & Object Tree

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

classDiagram
    direction TB
    class Customers {
        string CustomerID PK
        string CompanyName
        string ContactName
        string City
        string Country
    }
    class Orders {
        int OrderID PK
        string CustomerID FK
        int EmployeeID FK
        date OrderDate
        date ShippedDate
        int ShipVia FK
        decimal Freight
    }
    class OrderDetails {
        int OrderID PK, FK
        int ProductID PK, FK
        decimal UnitPrice
        smallint Quantity
        real Discount
    }
    class Products {
        int ProductID PK
        string ProductName
        int SupplierID FK
        int CategoryID FK
        decimal UnitPrice
        smallint UnitsInStock
    }
    class Categories {
        int CategoryID PK
        string CategoryName
        string Description
    }
    class Employees {
        int EmployeeID PK
        string LastName
        string FirstName
        string Title
        int ReportsTo FK
    }
    class Shippers {
        int ShipperID PK
        string CompanyName
        string Phone
    }

    Customers "1" --> "0..*" Orders : places
    Employees "1" --> "0..*" Orders : manages
    Shippers "1" --> "0..*" Orders : delivers
    Orders "1" --> "1..*" OrderDetails : contains
    Products "1" --> "0..*" OrderDetails : sold_in
    Categories "1" --> "0..*" Products : categorizes

Northwind (Database Object Hierarchy)
├── 📁 Base Tables (13 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 Reporting Views (Classic Microsoft Analytical Views)
    ├── 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

Option A: Fast Command-Line Setup via sqlcmd

Execute both scripts in seconds directly from PowerShell or Terminal:

# 1. Create empty Northwind database and execute instnwnd.sql
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. Execute instpubs.sql (creates and populates the pubs database automatically)
sqlcmd -S . -E -i "D:\courses\Data Analysis 26-27\instpubs.sql"

Option B: SQL Server Management Studio (SSMS) GUI

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

   CREATE DATABASE Northwind;
   GO
   

3. Open D:\courses\Data Analysis 26-27\instnwnd.sql in SSMS (File > Open > File...).
4. Verify the available database dropdown in the top-left toolbar is set to Northwind.
5. Press F5 (or click Execute). All 13 tables, 16 views, and stored procedures are created and populated.
6. Open D:\courses\Data Analysis 26-27\instpubs.sql $\to$ Press F5. The pubs database is created and populated.

📊 Connecting Northwind to the Modern Excel Stack

Northwind is the ultimate laboratory for bridging Excel Spreadsheets $\leftrightarrow$ SQL Relational Modeling $\leftrightarrow$ Power Pivot Star Schemas:

flowchart TD
    subgraph SQL_SERVER ["Microsoft SQL Server (Northwind)"]
        direction TB
        TBL_CUST["Customers (91)"]
        TBL_EMP["Employees (9)"]
        TBL_PROD["Products (77)"]
        TBL_ORD["Orders (830)"]
        TBL_DET["Order Details (2,155)"]
    end

    subgraph EXCEL_ETL ["Excel Power Query (ETL)"]
        PQ_CONN["Data > Get Data > From Database > From SQL Server\nSql.Database('.', 'Northwind')"]
    end

    subgraph POWER_PIVOT ["Power Pivot (VertiPaq Data Model)"]
        direction TB
        DIM_CUST[("Dim_Customer")]
        DIM_EMP[("Dim_Employee")]
        DIM_PROD[("Dim_Product")]
        FACT_ORD[("Fact_Orders")]
        FACT_DET[("Fact_OrderDetails")]

        DIM_CUST -->|"1 : N (CustomerID)"| FACT_ORD
        DIM_EMP -->|"1 : N (EmployeeID)"| FACT_ORD
        FACT_ORD -->|"1 : N (OrderID)"| FACT_DET
        DIM_PROD -->|"1 : N (ProductID)"| FACT_DET
    end

    subgraph REPORTING ["Excel PivotTable & Slicers"]
        PT["Interactive Sales Dashboard\n• Revenue by Country (Customer)\n• Quota by Rep (Employee)\n• Product Category Gross Margin"]
    end

    SQL_SERVER ==> EXCEL_ETL
    EXCEL_ETL ==> POWER_PIVOT
    POWER_PIVOT ==> REPORTING

    style SQL_SERVER fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style EXCEL_ETL fill:#fff3e0,stroke:#ef6c00,stroke-width:2px
    style POWER_PIVOT fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
    style REPORTING fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px

1. Eliminating the VLOOKUP Hell:

   • In traditional Excel, an analyst wanting to analyze Order Details by Customer Country, Product Category, and Sales Rep would write three separate =VLOOKUP or =XLOOKUP formulas repeated across 2,155 rows, dramatically bloating workbook size and slowing calculation.
   • In Power Pivot, loading Orders, [Order Details], Customers, Products, and Employees and establishing 1-to-many relationships allows instantaneous multi-dimensional cross-filtering with zero lookup formulas!

2. Ingesting Pre-Built Analytical Views:

   • Instead of writing complex aggregations in Excel, an analyst can directly query Northwind's built-in views:
     • Summary of Sales by Quarter: Returns pre-calculated quarterly totals.
     • Sales by Category: Returns departmental performance.
     • Invoices: Pre-joined view combining customer details, freight, items, and shipping addresses.

🧠 Bridging Excel to SQL & Relational Database Concepts

Transitioning from spreadsheet thinking to relational database thinking is the defining leap in a data analyst's career. Here is how core Excel operations translate to enterprise SQL concepts:

1. Spreadsheets vs Relational Databases (RDBMS)

2. How Quries.sql Bridges SQL to XML, JSON, and Excel

Notice how the script Quries.sql in our course materials demonstrates that SQL Server is the foundational source for multiple data formats:

flowchart TD
    AW[("AdventureWorks2022\n(SQL Server 2022)")]
    
    Q1["Query 1: FOR XML PATH('Product')\n(Production.Product)"]
    Q2["Query 2: FOR JSON PATH, ROOT('People')\n(Person.Person + Email + Phone)"]
    Q3["Query 3: Native Special Query\n(Person LEFT JOIN EmailAddress)"]
    
    F1["XML_F52E2B61-18A1-11d1-B105-00805F49916B1.xml\n(504 Products -> Ingested into Module 7)"]
    F2["JSON_F52E2B61-18A1-11d1-B105-00805F49916B3.json\n(19,972 People -> Ingested into Module 7)"]
    F3["Excel Live Query Table\n(~1,140 Rows Filtered at Database Server)"]
    
    AW --> Q1 --> F1
    AW --> Q2 --> F2
    AW --> Q3 --> F3

    style AW fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    style F1 fill:#fff3e0,stroke:#ef6c00,stroke-width:2px
    style F2 fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px
    style F3 fill:#f3e5f5,stroke:#6a1b9a,stroke-width:2px

1. FOR XML PATH: Translates tabular relational rows into hierarchical XML nodes with elements and attributes.
2. FOR JSON PATH: Formats rows into JSON arrays and objects, making database payloads ready for web APIs and microservices.
3. Pushdown SQL: Ingests filtered relational rows directly into Excel without converting to intermediate files.

3. Excel Formulas vs SQL Equivalents

4. Data Type Alignment Matrix

Understanding how SQL Server data types translate to Power Query M types and Excel worksheet formatting prevents truncation and type mismatch errors:

5. Star Schemas & The Power Pivot Connection

When you build a data model in Excel's Power Pivot, you are applying relational database principles directly inside the workbook:

• In relational databases, normalized tables are linked via Foreign Key constraints.
• In Power Pivot, those same foreign keys define 1-to-Many Relationships between Dimension Tables (e.g. Dim_Customer, Dim_Product, Dim_Date) and Fact Tables (e.g. Fact_Sales, Fact_Calls).
• This eliminates the need for thousands of =VLOOKUP() formulas that bloat file size and drag recalculation speeds to a crawl.

5. Ingestion Channels 8 & 9: Website Scraping and REST APIs

Channel 8: From Website (Live Case Study: Arabic Wikipedia Egyptian Demographics)

Web scraping inside Power Query is one of Excel's most formidable capabilities. Rather than manually copying and pasting web tables—which destroys data types, breaks cell alignments, and fails whenever online data updates—Power Query connects directly to live web URLs, interprets HTML Document Object Models (DOM), and extracts tabular elements dynamically.

🌐 Live Course Implementation: Demographics of Egypt (التركيبة السكانية في مصر)

In Module_7_Demo.xlsx, students extract live public demographic tables directly from Arabic Wikipedia:

• Source URL: https://ar.wikipedia.org/wiki/التركيبة_السكانية_في_مصر
• Target Subject: National demographic development, vital statistics, fertility rates, and governorate population distributions published by the Egyptian Central Agency for Public Mobilization and Statistics (CAPMAS).

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 Rates\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

📦 Extracted Demographic Tables & Schema Specifications

The live extraction in Module_7_Demo.xlsx parses five distinct demographic datasets using CSS selector bindings:

💻 Authentic Power Query M Scripts from Module_7_Demo.xlsx

1. Vital Statistics Extraction (Table 15):

shared #"Table 15" = let
    // 1. Fetch live rendered DOM via headless browser container
    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"),
    
    // 2. Query target HTML table by element ID using CSS column selectors
    #"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"]),
    
    // 3. Promote first row to Arabic column headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    
    // 4. Cast metric columns to numbers and integers
    #"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";

2. Governorate Demographics Extraction (Table 18):

shared #"Table 18" = let
    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"),
    #"Extracted Table From Html" = Html.Table(Source, {
        {"Column1", "TABLE[id='mwBrY'] > * > TR > :nth-child(1)"}, 
        {"Column2", "TABLE[id='mwBrY'] > * > TR > :nth-child(2)"}, 
        {"Column3", "TABLE[id='mwBrY'] > * > TR > :nth-child(3)"}, 
        {"Column4", "TABLE[id='mwBrY'] > * > TR > :nth-child(4)"}, 
        {"Column5", "TABLE[id='mwBrY'] > * > TR > :nth-child(5)"}
    }, [RowSelector="TABLE[id='mwBrY'] > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"المحافظة", type text}, 
        {"نسبة الحضر[21]", type number}, 
        {"عدد السكان (2024)", Int64.Type}, 
        {"عدد سكان الريف", Int64.Type}, 
        {"عدد سكان الحضر", Int64.Type}
    })
in
    #"Changed Type";

🧼 Web Ingestion Cleaning Considerations

Web scraping introduces unique data cleaning hurdles that every professional analyst must master:

1. Wikipedia Footnote References ([21], [a]):
   • In Table 18, the column header was imported as نسبة الحضر[21].
   • Fix: Rename column to نسبة الحضر or purge bracketed regex tokens using Text.Select / Text.BeforeDelimiter.
2. Hidden Linebreak Characters (#(lf)):
   • In Table 19, multi-line HTML table cells import embedded linebreaks: الكثافة السكانية#(lf)في المناطق المأهولة.
   • Fix: Use Power Query Transform > Clean or rename headers to eliminate embedded newline characters.
3. Thousand Separators & Comma Delimiters in Arabic Numbers:
   • Arabic Wikipedia articles often format large numbers with commas (104,462,545) or Arabic thousand separators (،).
   • If imported as type text, applying mathematical formulas triggers #VALUE!.
   • Fix: Replace commas Table.ReplaceValue(..., ",", "", Replacer.ReplaceText) before casting to Int64.Type.
4. Dynamic Refresh Advantage:
   • When CAPMAS publishes updated 2025 census numbers to the Wikipedia article, clicking Data > Refresh All in Excel automatically re-scrapes the webpage, runs all transformations, and updates the worksheet in seconds!

Channel 9: From REST API (Programmatic Web.Contents & JSON)

When services do not expose HTML tables or direct SQL connections, they provide REST APIs returning live JSON data.

Grounded Course Script: M language to Deal with API.txt

In this lesson demo, students connect Excel to a live currency exchange rate API (https://api.exchangerate-api.com/v4/latest/USD) using native Power Query M code:

let
    // 1. Declare base API endpoint and dynamic currency parameter
    baseUrl = "https://api.exchangerate-api.com/v4/latest/",
    currency = "USD",
    fullUrl = baseUrl & currency,

    // 2. Transmit HTTP GET Request via Web.Contents
    response = Web.Contents(fullUrl),

    // 3. Parse JSON stream into an M record object
    json = Json.Document(response),

    // 4. Navigate into the nested 'rates' record
    rates = json[rates],

    // 5. Convert JSON key-value pairs into a two-column Tabular Dataset
    table = Record.ToTable(rates)
in
    table

flowchart LR
    API["https://api.exchangerate-api.com\n(REST API Endpoint)"] --> WebC["Web.Contents(fullUrl)\nHTTP GET Request"]
    WebC --> JsonDoc["Json.Document(response)\nDeserializes JSON Stream"]
    JsonDoc --> Extract["json[rates]\nNavigates to Rates Dictionary"]
    Extract --> RecToTab["Record.ToTable(rates)\nConverts Key-Value Pairs to Table"]
    RecToTab --> ExcelGrid["Clean Excel Columns:\n[Name = Currency, Value = Rate]"]

    style API fill:#ffebee,stroke:#c62828
    style WebC fill:#fff3e0,stroke:#ef6c00
    style JsonDoc fill:#e1f5fe,stroke:#0288d1
    style Extract fill:#e8f5e9,stroke:#2e7d32
    style RecToTab fill:#d1c4e9,stroke:#512da8
    style ExcelGrid fill:#f3e5f5,stroke:#6a1b9a

How to Implement in Excel:

1. Open Data > Get Data > From Other Sources > Blank Query.
2. Click Advanced Editor in the Query tab.
3. Paste the M code snippet above and click Done.
4. Rename column Name to Target_Currency and Value to Exchange_Rate.
5. Click Close & Load. Your workbook now has live, real-time FX rates updated on demand!

6. Refresh Mechanics and Enterprise Pipeline Governance

Once an ingestion pipeline is established, analysts configure refresh rules to automate ongoing updates:

7. Comparative Architecture Matrix

Related Knowledge

• Notes: 01 Data Quality Dimensions and Audit, 02 Data Cleaning Techniques in Excel, 04 Business Systems for Analysts
• Concepts: Power Query, ETL Process, M Language, Data Cleaning
• Course Demos & Scripts:
  • SQL: 09_Source_Materials/Module 7/2-Importing Data/Quries.sql
  • Power Query M: 09_Source_Materials/Module 7/2-Importing Data/M language to Deal with API.txt
  • Datasets: Supermarket data.csv, Sales.xlsx, XML_F52E2B61-18A1-11d1-B105-00805F49916B1.xml