COM3MN209 • Business Analytics Tools

Module 3

Calicut University • B.Com • Semester 3

Business Analytics Tools (COM3MN209) — Module 3: Searching and Combining Data with Power Query

Lecture Notes • Complete Study Material

Executive Summary & Technical ArchitectureCALICUT UNIVERSITY • B.COM HONOURS

In real-world business analytics, data rarely arrives pristine, clean, and neatly structured. Corporate transactional data is dispersed across disparate relational databases, enterprise ERP warehouses, public cloud APIs, messy monthly CSV files, and web pages. Manually copying, reformatting, and merging these datasets every week consumes hundreds of billable human hours and introduces fatal errors. This module delivers an exhaustive, practical mastery of Power Query—the industry-standard Extract, Transform, and Load (ETL) data mashup engine embedded inside Microsoft Excel and Power BI—and Structured Query Language (SQL), the universal standard for enterprise database querying.

3.1 Getting Started with Power Query: Interface, Architecture & M Language

The Extract, Transform, Load (ETL) Paradigm

Power Query operates on the foundational principles of enterprise data warehousing known as ETL:

[EXTRACT] Connect to heterogeneous external sources: • Local files: Excel, CSV, Text, XML, JSON, PDF tables, Folder • Databases: Microsoft SQL Server, Oracle, MySQL, PostgreSQL, Access • Cloud & Web: SharePoint, Web HTML Tables, OData feeds, Salesforce | v [TRANSFORM] Non-destructive data manipulation inside the Power Query Engine: • Remove corrupt rows, promote headers, convert data types • Split columns, unpivot attributes, replace errors, fill down nulls • Merge queries (Relational Joins), Append queries (Unions), Group By aggregations | v [LOAD] Deliver sanitized, pristine tabular data to destination: • Load directly to an active Excel Worksheet table (ListObject) • Load into the Excel Data Model (Power Pivot xVelocity in-memory database) • Create Connection-Only query to conserve computer RAM

The Non-Destructive Transformation Engine

The single most revolutionary architectural advantage of Power Query over traditional spreadsheet manipulation is its non-destructive, declarative audit trail. When an analyst deletes columns, filters rows, or changes data types in Power Query:

Original Source Files Remain 100% Unmodified: Power Query never alters, overwrites, or damages the underlying source CSV, database table, or Excel workbook. It reads data into an isolated memory buffer.
The Applied Steps Audit Recipe: Every single transformation action executed by the user is recorded sequentially in the Applied Steps pane. This creates a repeatable visual recipe. When next month’s updated transaction file arrives, clicking Refresh All causes Power Query to automatically execute all 25 transformation steps in milliseconds without requiring any human re-work.
Step Reordering and Inspection: Analysts can click on any intermediate step in the Applied Steps list to view the exact state of the data at that specific historical point in the transformation pipeline, or delete/reorder steps with complete surgical safety.

The Power Query User Interface Architecture

The Power Query Editor window is organized into five tightly coordinated functional zones:

Interface Component	Location & Operational Control	Analytical Management Function
The Ribbon (Tabs & Toolbars)	Top navigation banner: Home, Transform, Add Column, View tabs.	Provides one-click graphical access to over 300 data transformation and cleaning algorithms.
Queries Pane	Collapsible vertical sidebar on the extreme left.	Displays all active data queries, staging queries, custom functions, and parameter tables in the workbook.
Data Preview Grid	Central large interactive spreadsheet grid.	Displays a live visual preview of the first 1,000 rows of transformed data, complete with column quality profiling bars.
Formula Bar	Positioned directly above the central data preview grid.	Displays the underlying M code formula executed by the currently active transformation step; allows direct formula editing.
Applied Steps Pane	Vertical sidebar on the extreme right (under Query Settings).	Maintains the chronological, immutable list of transformation steps; allows renaming, deleting, and reconfiguring steps.

Introduction to the M Formula Language

Behind every button clicked on the Power Query ribbon lies an advanced, functional, case-sensitive programming language known officially as the Power Query M Formula Language (short for Data Mashup). M is a declarative, pure functional language structured around the classical let ... in ... evaluation construct:

Anatomy of an M Language Query:

let
    Source = Csv.Document(File.Contents("C:DataSales2026.csv"), [Delimiter=",", Columns=4, Encoding=65001]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders, {{"SaleDate", type date}, {"Revenue", type number}}),
    FilteredRows = Table.SelectRows(ChangedType, each [Revenue] > 1000)
in
    FilteredRows

Key M Syntax Rules:
• Case Sensitivity: M is strictly case-sensitive. The function Table.SelectRows will execute properly, whereas table.selectrows or Table.selectRows will crash with syntax errors.
• Step Chaining: Each line in the let block creates an intermediate variable that references the preceding step's output name. The in clause declares which final variable is returned to the user.

3.2 Accessing, Cleansing & Combining Heterogeneous Datasets

Comprehensive Data Ingestion Capabilities

Power Query provides native, enterprise-grade connectors to ingest data from hundreds of disparate commercial systems:

From Folder Connector: The ultimate weapon for recurring monthly reporting. Instead of opening and combining twelve separate monthly Excel or CSV files, the analyst points Power Query to a network folder directory. Power Query automatically reads, combines, and normalizes all files in that folder into one continuous master table. When month thirteen's file is dropped into the folder, clicking Refresh automatically incorporates it.
From Web Connector: Ingests structured HTML data tables directly from live web pages (e.g., central bank foreign exchange rates, stock market commodity quotes) by supplying the public URL.
From Database Connector: Connects to enterprise relational databases (SQL Server, Oracle, PostgreSQL). Provides an interface to visually select database tables or write direct custom SQL queries.

Essential Data Cleansing Operations

Cleansing Operation	Operational Mechanism in Power Query	Business Data Hygiene Purpose
Promote Headers	Home Tab > Use First Row as Headers.	Converts default raw column labels ("Column1", "Column2") into true commercial field names.
Fill Down / Fill Up	Transform Tab > Fill > Down.	Resolves merged cell report dumps where customer names appear once above dozens of blank invoice rows.
Unpivot Columns	Transform Tab > Unpivot Columns / Unpivot Other Columns.	Converts human-readable cross-tab matrix reports (e.g., 12 monthly columns) into tall, normalized database records.
Split Column	Home Tab > Split Column > By Delimiter (comma, hyphen, space).	Decomposes composite fields: separating "John_Doe_Sales" into First Name, Last Name, and Department fields.
Text Hygiene (Trim / Clean)	Transform Tab > Format > Trim / Clean / Capitalize Each Word.	Strips invisible trailing whitespace and unprintable line-break characters that cause VLOOKUP and join mismatches.

Relational Query Merging (Joins) vs. Appending (Unions)

Power Query provides two distinct mathematical methods for synthesizing multiple tables:

Appending Queries (Union - Stacking Rows)

Vertical Stacking: Appending combines two or more tables that share identical column headers by stacking their rows vertically.

Example: Combining January Sales (10,000 rows) with February Sales (12,000 rows) results in a unified consolidated table of 22,000 rows. Columns must have matching names and data types.

Merging Queries (Joins - Combining Columns)

Horizontal Synthesis: Merging connects two tables horizontally based on a shared common key column (Primary / Foreign Key), similar to an automated multi-attribute VLOOKUP.

Example: Joining a Sales Transactions table with a Customer Master table using CustomerID to pull in customer demographics.

The Six Relational Join Types in Power Query

Join Type	Relational Set Theory Mechanics	Commercial Business Application
Left Outer Join	All rows from the first (left) table, and only matching rows from the second (right) table. (Default join).	Evaluating all sales orders, pulling in customer details where available, retaining orders even if customer profile is unlinked.
Right Outer Join	All rows from the second (right) table, and only matching rows from the first (left) table.	Auditing a supplier master catalog, identifying which suppliers generated zero sales during the quarter.
Full Outer Join	All rows from both tables. Unmatched rows on either side are populated with null values.	Reconciling two corporate bank account statements to find discrepancies on both sides.
Inner Join	Only rows that have exact matching keys in both left and right tables. All non-matching records discarded.	Reporting only completed transactions where both verified product and verified customer IDs exist.
Left Anti Join	Only rows in the first (left) table that have NO matching key in the second (right) table.	Identifying customers who registered on the app but have placed zero purchase orders (targeting churn campaigns).
Right Anti Join	Only rows in the second (right) table that have NO matching key in the first (left) table.	Isolating inventory products in warehouse catalogs that recorded zero customer sales during the fiscal year.

3.3 Querying Enterprise Data with SQL: The SELECT Engine

Foundational Architecture of Relational Database Management Systems (RDBMS)

An enterprise database stores information in normalized relational tables comprising vertical columns (fields or attributes) and horizontal rows (records or tuples). Tables maintain relationships through:

Primary Key (PK): A unique column (or combination of columns) that uniquely identifies each individual row in a table (e.g., CustomerID, InvoiceNumber). Primary keys can never contain null values or duplicate entries.
Foreign Key (FK): A column in one table that points directly to the Primary Key of another table, establishing a verified relational link (e.g., CustomerID placed inside the Orders table).

The Anatomy of the SQL SELECT Statement

Structured Query Language (SQL) is the universal declarative language for database interaction. The foundational command for data extraction is the SELECT statement:

Fundamental SQL Query Syntax:

SELECT column1, column2 AS AlternateName, (UnitPrice * Quantity) AS TotalAmount
FROM Sales_Transactions
WHERE Status = 'Completed' AND OrderDate >= '2026-01-01'
ORDER BY TotalAmount DESC;

Core Clauses:
• SELECT: Declares the specific columns, calculated expressions, or aggregations to retrieve.
• FROM: Designates the source database table or joined tables containing the data.
• AS (Aliasing): Renames columns in the output result set for executive readability.
• DISTINCT: Eliminates duplicate rows, returning only unique values (e.g., SELECT DISTINCT City FROM Stores;).

Filtering Query Results with the WHERE Clause

The WHERE clause specifies boolean criteria that every row must satisfy to be included in the analytical output:

Operator / Predicate	SQL Syntax Example	Managerial Business Logic
Comparison Operators	`WHERE UnitPrice >= 500 AND StockQty < 20`	Isolates high-value merchandise facing immediate warehouse stockout risk.
Range Filtering (BETWEEN)	`WHERE OrderDate BETWEEN '2026-01-01' AND '2026-03-31'`	Extracts transactions occurring strictly within First Quarter (Q1) boundaries (inclusive).
List Membership (IN)	`WHERE Region IN ('South', 'West', 'North')`	Efficient alternative to multiple chained OR clauses; filters for specified geographical territories.
Pattern Matching (LIKE)	`WHERE CustomerName LIKE 'A%' OR Email LIKE '%@gmail.com'`	`%` matches any character string; `_` matches a single character. Finds names starting with A.
Null Evaluation (IS NULL)	`WHERE DeliveryDate IS NULL`	Identifies pending customer orders that have not yet been dispatched by shipping logistics.

3.4 Managing SQL Commands, Aggregations & Table Management

Sorting Data with the ORDER BY Clause

By default, relational databases return query rows in unpredictable physical storage order. The ORDER BY clause enforces deterministic sorting:

ORDER BY Revenue DESC: Sorts output in descending numerical sequence (largest to smallest).
ORDER BY Region ASC, Revenue DESC: Multi-level sort; orders records alphabetically by Region, and within each regional cluster, sorts sales descending from highest to lowest.

Data Aggregation: GROUP BY and the HAVING Clause

Analytical queries frequently require aggregating millions of transaction rows into regional or category summaries using standard SQL aggregate functions: COUNT(), SUM(), AVG(), MIN(), and MAX().

The GROUP BY & HAVING Architecture:

SELECT CategoryName, COUNT(ProductID) AS TotalSKUs, SUM(SalesAmount) AS TotalRevenue, AVG(UnitPrice) AS AvgPrice
FROM Product_Sales
WHERE OrderStatus = 'Shipped'
GROUP BY CategoryName
HAVING SUM(SalesAmount) > 500000
ORDER BY TotalRevenue DESC;

Crucial Distinction: WHERE vs. HAVING:
• WHERE filters individual raw rows before mathematical grouping occurs.
• HAVING filters aggregated summary groups after the GROUP BY aggregation has executed. You cannot use WHERE to filter aggregated sums.

Relational SQL Joins

Normalized enterprise databases distribute data across multiple tables to eliminate redundancy. SQL Joins synthesize these tables in real time:

SQL Join Syntax	Relational Execution Logic
`INNER JOIN`	`SELECT O.OrderID, C.CustomerName, O.OrderAmount FROM Orders O INNER JOIN Customers C ON O.CustomerID = C.CustomerID;` Returns only orders that possess a valid, matching customer record in the Customers master table.
`LEFT JOIN`	`SELECT C.CustomerName, O.OrderID, O.OrderAmount FROM Customers C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID;` Returns all customers from table C, showing order details where orders exist, or displaying NULL if the customer has placed zero orders.

Data Definition Language (DDL) and Data Manipulation Language (DML)

SQL commands are divided into structural definition commands and data manipulation commands:

DDL (Data Definition Language): Governs database table architecture:
- CREATE TABLE Employees (EmpID INT PRIMARY KEY, Name VARCHAR(50), Salary DECIMAL(10,2));
- ALTER TABLE Employees ADD Department VARCHAR(30);
- DROP TABLE Temp_Sales; (Permanently deletes table structure and all data).
DML (Data Manipulation Language): Governs the data rows within tables:
- INSERT INTO Employees (EmpID, Name, Salary) VALUES (101, 'Aravind Kumar', 65000.00);
- UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'Analytics';
- DELETE FROM Employees WHERE ResignationDate < '2025-01-01';

Query Folding: The Power Query - SQL Synergistic Bridge

When an analyst connects Power Query to an SQL database and applies visual transformation steps (such as filtering rows or grouping by category), Power Query does not download the entire multi-gigabyte table to the local computer. Instead, Power Query utilizes Query Folding: it automatically translates the visual M transformation steps back into a single, optimized native SQL query that executes directly on the high-performance database server. Only the final, filtered summary rows are transmitted over the corporate network, delivering blistering analytical speed.

3.5 Advanced Data Reshaping: The Power of Unpivoting

Wide Formats vs. Tall Normalized Database Schemas

Human beings prefer reading "wide" matrix tables where months or fiscal quarters are arranged horizontally across columns. However, relational database engines, Pivot Tables, and analytical BI tools require data formatted as "tall" normalized records:

[HUMAN-READABLE WIDE MATRIX (Hard to analyze with Pivot Tables)] Product | Jan-Sales | Feb-Sales | Mar-Sales Laptop | 100,000 | 120,000 | 110,000 Smartphone | 250,000 | 280,000 | 310,000 || POWER QUERY "UNPIVOT COLUMNS" / [NORMALIZED TALL DATABASE SCHEMA (Ideal for Pivot Tables & SQL)] Product | Month | SalesAmount Laptop | Jan-Sales | 100,000 Laptop | Feb-Sales | 120,000 Laptop | Mar-Sales | 110,000 Smartphone | Jan-Sales | 250,000 Smartphone | Feb-Sales | 280,000 Smartphone | Mar-Sales | 310,000

PRACTICAL WORKED WALKTHROUGH: UNPIVOT OTHER COLUMNS TECHNIQUE

When transforming wide financial schedules where new monthly columns are appended dynamically every month:

The Danger of "Unpivot Columns": If an analyst selects columns "Jan" through "Dec" and clicks Unpivot Columns, the generated M formula hardcodes those exact column names. When "Jan-NextYear" is added to the source, Power Query ignores it.

The Professional Solution - "Unpivot Other Columns": Instead of selecting the month columns, select the static identifier columns (e.g., ProductID and ProductName), right-click, and select Unpivot Other Columns. Power Query generates an M formula that dynamically unpivots any and all future columns that appear in the dataset, ensuring a 100% future-proof automated reporting pipeline.

3.6 Advanced SQL Analytics: Subqueries, CTEs & Window Functions

Common Table Expressions (CTEs): The WITH Construct

In complex business analytics, nesting subqueries five levels deep creates indecipherable, unmaintainable code. Common Table Expressions (CTEs) allow analysts to define temporary, named result sets that can be referenced within the main query, radically improving code modularity:

CTE Syntax in Corporate Sales Analytics:

WITH RegionalSummaries AS (
    SELECT Region, Department, SUM(SalesAmount) AS DeptRevenue
    FROM Transaction_Log
    WHERE FiscalYear = 2026
    GROUP BY Region, Department
),
RegionalRankings AS (
    SELECT Region, Department, DeptRevenue,
    RANK() OVER (PARTITION BY Region ORDER BY DeptRevenue DESC) AS RevenueRank
    FROM RegionalSummaries
)
SELECT * FROM RegionalRankings WHERE RevenueRank <= 3;

Business Output: Isolates the top 3 highest-earning product departments for every geographic operating region in a clean, self-documenting query structure.

Analytical Window Functions in SQL

Unlike traditional GROUP BY aggregations—which collapse individual rows into a single summary row—SQL Window Functions perform calculations across a set of table rows related to the current row while preserving each individual row's distinct identity:

Window Function	Standard SQL Syntax	Executive Analytical Utility
`ROW_NUMBER()`	`ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC)`	Assigns unique sequential integers to customer orders; isolates the single most recent order per customer (where RowNum = 1).
`RANK() vs. DENSE_RANK()`	`DENSE_RANK() OVER (ORDER BY TotalRevenue DESC)`	Ranks sales executives by revenue. `RANK` leaves gaps after ties (1, 2, 2, 4); `DENSE_RANK` avoids gaps (1, 2, 2, 3).
`Running Total (Cumulative SUM)`	`SUM(DailySales) OVER (ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`	Calculates a running year-to-date (YTD) cumulative revenue total across daily transaction records.

3.7 Troubleshooting & Error Handling in Power Query

Resolving Data Pipeline Failures

Enterprise data pipelines inevitably encounter runtime exceptions when upstream systems alter data schemas:

Handling Type Conversion Errors ([DataFormat.Error]): Occurs when numerical columns contain text annotations (e.g., "N/A" or "TBD" inside price columns). Remedy: Right-click column > Replace Errors with 0, or convert to type Text, cleanse anomalies with conditional logic, and then convert to Number.
Missing Columns in Appended Files: If a vendor renames column "Customer_ID" to "Cust_No", Power Query will fail to combine the tables. Analysts utilize the optional M parameter MissingField.Ignore or rename columns dynamically before concatenation.
The Formula.Firewall Error: Triggered when a query combines data from a secure internal database with an unsecured external web API. Power Query’s security engine blocks execution to prevent data exfiltration. Remedy: Navigate to Query Options > Privacy > Configure Privacy Levels to "Organizational" across both sources, or create an intermediate buffer query using Table.Buffer().

3.8 Enterprise SQL Architecture: Indexing & ACID Transactions

Database Performance Optimization via Indexing

When querying multi-million-row corporate databases, executing a full table scan for every query paralyzes system throughput. Database administrators construct Indexes—specialized B-Tree lookup data structures that dramatically accelerate search retrieval:

Clustered Index: Defines the physical storage order of data rows on the hard drive disk. Because physical data can only be sorted in one way, a table can possess only one clustered index (typically assigned automatically to the Primary Key).
Non-Clustered Index: A separate auxiliary structure containing sorted key values alongside pointers (Row Locators) back to the actual data row. A table can possess multiple non-clustered indexes (e.g., creating non-clustered indexes on CustomerID and TransactionDate to accelerate frequent reporting queries).

ACID Properties of Enterprise Relational Transactions

In retail point-of-sale and financial transaction systems, data integrity is guaranteed through the rigorous mathematical enforcement of the ACID Framework:

ACID Principle	Technical Database Mechanism	Commercial Banking / Retail POS Safeguard
Atomicity ("All or Nothing")	The entire multi-step transaction completes successfully, or it is completely rolled back to its initial state.	If money is debited from a customer's bank account but an ATM power failure interrupts cash dispensing, the transaction is automatically rolled back, refunding the account.
Consistency	The transaction transitions the database from one valid state to another, preserving all relational constraints and foreign keys.	Prevents creating an order record pointing to a non-existent customer ID; enforces that total debits equal total credits.
Isolation	Concurrent transactions execute in complete isolation without reading uncommitted dirty intermediate data.	Prevents two concurrent shoppers from purchasing the last remaining seat on an airline flight at the exact same millisecond.
Durability	Once a transaction is committed, its updates are permanently recorded in non-volatile storage and will never be lost, even during server crashes.	Transaction logs written to mirrored disk arrays guarantee that finalized POS revenue records survive sudden power blackouts.

COM3MN209Business Analytics Tools

Download Module 3 Notes (PDF)

Calicut University • FYUGP 2024 Syllabus

Download PDF

Finished this module?

Continue reading the next module or return to the subject overview.

All Modules Next: Module 4