Applied Business Integration Project

Introduction

A Power BI solution is only as trustworthy as the data integration process beneath it. In professional settings, managers rarely provide a perfectly structured dataset. Instead, an analyst may receive sales transactions from an enterprise resource planning system, product information from a spreadsheet, targets from a planning workbook, and exchange rates from an external service. These sources may use different names, formats, grain, refresh schedules, and definitions.

This chapter presents an end-to-end method for converting a managerial information requirement into a governed Power Query solution. The emphasis is not on isolated transformations, but on the complete integration lifecycle: defining the decision need, acquiring data, profiling its quality, transforming and combining sources, validating results, documenting design decisions, refreshing reliably, and governing the resulting data product.

The central principle is that integration is an information-quality activity, not merely a technical activity. A query can refresh successfully and still produce misleading management information if it duplicates transactions, applies an incorrect exchange rate, or joins tables at incompatible grains.

Learning Objectives

By the end of this chapter, you should be able to:

1. Translate a managerial question into data requirements, measures, grains, and acceptance criteria.
2. Design a multi-source Power Query architecture using staging, transformation, and output layers.
3. Profile source data and identify structural, completeness, validity, consistency, and duplication problems.
4. Apply appropriate Power Query transformations, joins, appends, parameters, and error-handling techniques.
5. Validate integrated data using reconciliation, uniqueness, referential-integrity, and reasonableness tests.
6. Document assumptions, data lineage, refresh dependencies, and ownership responsibilities.
7. Evaluate refresh, security, privacy, governance, and operational risks.

1. From Managerial Requirement to Data Specification

A request such as “provide a monthly regional sales dashboard” is not yet an integration specification. It must be translated into explicit questions:

• What decision will the report support?
• What does “sales” mean: invoiced value, shipped value, or cash received?
• What is the reporting period and time zone?
• What is the required grain: one row per invoice line, invoice, customer, or month?
• Which dimensions are required: product, region, channel, customer, and salesperson?
• Which targets or comparison periods are needed?
• How current must the data be?
• What level of accuracy and completeness is acceptable?

A useful requirements statement identifies the subject, measure, dimensions, filters, frequency, owner, and acceptance criteria. For example:

Regional sales managers require weekly invoiced revenue by region, product category, and sales channel, compared with approved weekly targets. The report must exclude cancelled invoices, use the corporate exchange-rate policy, refresh by 07:00 each weekday, and reconcile to the finance control total within an agreed tolerance.

This statement leads to a data specification. The specification should record source systems, fields, definitions, expected types, keys, business rules, and quality tests. It should also state the unit of analysis. If the sales fact is at invoice-line level, a product table must not be joined using a key that creates multiple product matches.

Grain and the Risk of Double Counting

Grain describes what one row represents. A transaction table might have one row per invoice line, while a target table might have one row per region and week. Joining these tables directly on region alone can multiply target values across every transaction. The correct approach is usually to aggregate the transaction fact to the target grain or maintain separate fact tables connected through shared dimensions.

Before combining tables, ask:

1. What does one row represent in each table?
2. Is the join key unique on the lookup side?
3. Is the relationship one-to-one, many-to-one, or many-to-many?
4. Will the operation change row count or monetary totals?

2. A Layered Power Query Architecture

A maintainable solution separates acquisition from business logic. A practical architecture contains four layers:

Queries that only acquire and lightly standardise data should generally not be loaded to the model. They can be connection-only staging queries. Reference queries are useful when several outputs depend on a common cleaned source; duplicated logic should be avoided because it increases maintenance and inconsistency risk.

A typical design may include:

• stg_SalesFiles: combines files and preserves source filename.
• stg_Products: imports and standardises the product master.
• stg_Targets: imports approved targets.
• tr_Sales: validates fields, removes cancelled records, and applies currency rules.
• tr_Targets: validates target grain and period.
• dim_Product: creates a unique product dimension.
• fact_Sales: produces the analysis-ready transaction table.
• qa_SalesExceptions: records rejected or suspicious rows.

This structure creates lineage: a user can trace an output field back through transformations to its originating source.

3. Acquiring Data from Multiple Sources

Power Query supports workbooks, text files, folders, databases, SharePoint, web services, and many other connectors. Selection should be based on reliability and governance rather than convenience alone.

A folder connector can be appropriate for recurring monthly files, but only if the folder is controlled. The solution should filter by extension, exclude temporary files such as ~$Budget.xlsx, and confirm that all files follow an agreed schema. Combining files from a folder without checking headers can silently introduce missing or renamed columns.

Database connections are generally preferable for high-volume, governed operational data because filtering and aggregation may be performed by the source system. However, the analyst must understand permissions, query folding, performance, and change management.

Parameters should hold values likely to change, such as:

• source folder path;
• server and database name;
• reporting start date;
• target currency;
• API environment;
• permitted file pattern.

Hard-coding these values inside several queries makes deployment and testing more difficult.

Example: Controlled Folder Ingestion

A robust folder ingestion process normally:

1. Connects to the approved folder.
2. Filters to the required extension and naming pattern.
3. Excludes hidden and temporary files.
4. Invokes a sample-file transformation function.
5. Adds source filename and modified timestamp.
6. Checks that expected columns exist.
7. Routes invalid files to an exception report rather than silently discarding them.

The filename is valuable audit metadata. It allows an analyst to identify which source file produced a questionable row.

4. Profiling Before Transformation

Profiling is the systematic examination of structure and quality before business rules are applied. Power Query provides column distribution, column quality, data profiling, value counts, and error indicators. These features should be supplemented with explicit tests.

Important profiling dimensions include:

Completeness

Are required values present? A null customer identifier may prevent a valid relationship. A missing transaction date may prevent period reporting.

Validity

Do values conform to permitted formats and domains? Examples include invalid dates, negative quantities where not allowed, unknown currency codes, and malformed product identifiers.

Consistency

Do different sources use compatible representations? One file may use UK, another GB, and a third United Kingdom. Product descriptions may differ in spelling while product codes remain stable.

Uniqueness

Are keys unique where uniqueness is expected? A product master containing two active records for one product code creates ambiguous joins.

Timeliness

Does the source contain the expected reporting period? A file received late or a database extract that stopped yesterday may produce an apparently complete but operationally stale report.

Conformity and data types

Text, number, date, datetime, and logical values must be assigned deliberately. Automatic type detection is useful initially but should not replace explicit type setting. Locale is especially important: 03/04/2025 can mean 3 April or 4 March depending on regional convention.

A profiling register should record the test, expected result, actual result, severity, and action. For example:

5. Transformation Patterns in Power Query

Power Query transformations should express business meaning clearly and should be ordered to reduce errors and improve performance.

Standardise Names and Types

Rename columns to consistent, meaningful names and apply explicit types. Avoid ambiguous names such as Value or Date when a more precise name such as NetSalesAmount or InvoiceDate is possible.

Clean Text Carefully

Use trimming and cleaning operations for accidental whitespace and non-printing characters. Do not remove meaningful leading zeroes from codes. A product code 00125 is not equivalent to the number 125.

Handle Nulls Deliberately

Replacing every null with zero is dangerous. A null quantity may mean “not supplied,” whereas zero means “measured and none.” The treatment must follow the field definition.

Filter According to Business Rules

For example, cancelled invoices may be excluded from recognised sales, but retained in an audit table. Filtering them out without documenting the rule weakens traceability.

Append and Merge Correctly

An append adds rows from tables with compatible columns. It is appropriate for January, February, and March transaction files. A merge joins columns using key values. It is appropriate for adding product category to sales, provided the product key is unique in the product table.

Use Mapping Tables Instead of Long Conditional Logic

A maintained region mapping table is usually preferable to a long series of nested conditional statements. Mapping tables can include effective dates, owners, and approval status.

Preserve Exceptions

A professional solution does not hide all bad records. It separates valid output from exceptions, including a reason such as MissingProduct, InvalidCurrency, or DuplicateKey. Exceptions can then be investigated and corrected at source.

Example M Code Pattern

The following illustrative M pattern adds a validation status rather than silently removing records:

let
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    Raw = Source{[Item="Sales", Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(
        Raw,
        {{"InvoiceID", type text}, {"ProductCode", type text},
         {"InvoiceDate", type date}, {"NetAmount", Currency.Type}},
        "en-GB"
    ),
    Status = Table.AddColumn(
        Typed,
        "ValidationStatus",
        each if [InvoiceID] = null then "MissingInvoice"
             else if [ProductCode] = null then "MissingProduct"
             else if [NetAmount] = null then "MissingAmount"
             else "Valid",
        type text
    ),
    ValidRows = Table.SelectRows(Status, each [ValidationStatus] = "Valid")
in
    ValidRows

The exact syntax depends on the source schema and locale. The pattern illustrates explicit typing, validation, and separation of valid rows from exception logic. In production, the exception rows should also be retained through a separate reference query.

6. Validation and Reconciliation

Validation is evidence that the output is fit for its intended use. It is not the same as checking whether a query completed without an error.

Structural Validation

Check that required columns exist, data types are correct, and expected tables are returned. A schema change should create a visible failure or warning, not silently produce null columns.

Row-Count Validation

Compare source, accepted, rejected, and output counts. If 100,000 source rows become 80,000 valid rows, the 20,000 rejected rows require an explanation.

Key and Relationship Validation

Test that dimension keys are unique and that fact keys have matching dimension records where required. An unmatched product may be assigned an explicit “Unknown” member, but this should be monitored rather than concealed.

Duplicate Detection

The correct duplicate test depends on grain. If a transaction is uniquely identified by invoice number and line number, test the combined key, not invoice number alone.

Reconciliation

For monetary data, reconcile integrated values to an authoritative control total. A difference may arise from excluded cancellations, currency conversion, timing, tax treatment, or duplicate removal. Each difference needs a documented explanation.

For a simple control total, the difference is:

The variables are monetary totals expressed in the same currency, period, and inclusion scope. The equation is meaningful only when both totals use the same definition. A relative difference can be calculated as:

This measure is dimensionless. It should not be used when the control total is zero; in that case, use the absolute difference and investigate whether a zero denominator reflects a genuine absence of activity.

Reasonableness Tests

Reasonableness tests identify unusual but technically valid results. Examples include:

• sales outside the expected date range;
• unusually high transaction values;
• negative quantities;
• a sudden zero-sales region;
• exchange rates far outside the normal range.

These tests do not prove that data is wrong. They identify cases requiring review.

7. Currency, Dates, and Business Definitions

Currency conversion is a frequent source of integration error. A solution must define whether an amount is converted using the transaction-date rate, invoice-date rate, month-end rate, or a rate supplied by finance. It must also define the direction of the rate.

If one unit of source currency equals r units of reporting currency, then:

If the rate instead expresses source currency units per one reporting-currency unit, the operation is division. The rate definition and units must therefore be recorded in the data dictionary.

Dates require similar care. A date-only field, a local datetime, and a UTC datetime are not interchangeable. The integration specification should identify the business time zone, period-closing rule, fiscal calendar, and treatment of late-arriving transactions.

Definitions also matter. “Revenue,” “orders,” “customers,” and “margin” may have different definitions in sales, finance, and operations. Power Query cannot resolve an organisational disagreement through syntax. The definition must be agreed, documented, and approved by the relevant owner.

8. Illustrative Case: Northstar Distribution

This is an illustrative case created for educational purposes.

Background

Northstar Distribution operates in three countries. Regional managers receive a weekly sales workbook, but the workbook is assembled manually from a transactional database, a product spreadsheet, and a target workbook. Managers suspect that reported sales are overstated and that some products are assigned to the wrong category.

Problem

The requested dashboard must show weekly invoiced sales by region, category, and channel, compared with approved targets. The existing process has four weaknesses:

1. Product descriptions, rather than stable product codes, are used for matching.
2. The target workbook contains one row per region and week, but it is joined to transaction rows before aggregation.
3. Cancelled invoices are removed without an audit record.
4. Currency conversion uses a manually typed monthly rate.

Analysis

The analyst defines the sales grain as one invoice line. The target grain is region-week. The product master should contain one active row per product code. The authoritative control total is the finance-approved invoiced amount by currency and week.

Profiling finds 2,400 sales rows, 35 missing product codes, two duplicate active product codes, and 18 cancelled invoices. The target table contains 12 region-week rows, as expected. The product duplicates would make a merge multiply some sales rows, while the direct target join would repeat targets for every invoice line.

Approach and Solution

The solution uses the following sequence:

1. Import database transactions, product master, targets, and approved exchange rates into staging queries.
2. Add source-system and extraction-time metadata.
3. Explicitly set data types using the agreed locale.
4. Create a product exception query for missing and duplicate product codes.
5. Obtain master-data owner approval for the duplicate product records.
6. Merge only to a product table containing one approved active row per product code.
7. Retain cancelled invoices in an audit output and exclude them from recognised sales according to the finance rule.
8. Convert amounts using the approved transaction-date exchange-rate table.
9. Aggregate sales to region-week-category-channel before comparing with targets.
10. Validate row counts, unmatched products, duplicate keys, date coverage, currency coverage, and finance control totals.
11. Publish a dashboard only when critical tests pass; otherwise publish an exception status.

A simplified output model contains a sales fact table, product and date dimensions, a target fact table, and QA tables. Targets are not duplicated across transaction rows. Instead, comparison is performed at a compatible reporting grain.

Outcome

The revised process identifies that the original workbook overstated target attainment because targets were repeated across invoice lines. It also identifies a product-master defect and makes rejected and cancelled records visible. The final report is more defensible because users can trace values to sources and understand the rules applied.

Learning Points

• Correct grain is as important as correct syntax.
• A successful refresh is not proof of a correct result.
• Exception tables support accountability rather than reducing confidence.
• Reference data and business definitions require named ownership.
• Reconciliation should be designed before publication, not added after a problem occurs.

9. Refresh, Deployment, and Operational Design

A refreshable solution must be designed for its operating environment. Consider gateway requirements for on-premises sources, credentials, privacy levels, scheduled refresh frequency, timeout limits, API throttling, and source availability.

Refresh dependencies should be documented in order. For example, a rate table must be available before currency conversion can complete. A source file may need to be closed or moved to an approved folder before ingestion. Incremental processing may be appropriate for large transaction histories, but its date boundaries must be tested so that late-arriving records are not permanently omitted.

A refresh log should record:

• start and end time;
• source status;
• row counts;
• rejected-row counts;
• reconciliation results;
• warnings and errors;
• responsible owner.

Refresh failure should have a defined response. The report may retain the last successful dataset, display its data-as-of timestamp, and notify an owner. It should not imply that current information is available when the latest refresh failed.

10. Documentation and Governance

Documentation converts an individual solution into an organisational asset. At minimum, maintain:

• a requirements statement;
• source inventory and ownership register;
• data dictionary;
• transformation and business-rule register;
• lineage description;
• test and reconciliation log;
• refresh schedule and support procedure;
• change log;
• privacy and access assessment.

Governance should assign responsibility. Data owners approve definitions and source quality; the report owner approves business use; the technical owner maintains queries and refresh; information-security staff advise on access and sensitive data.

Personal data should be minimised, protected, and retained only as necessary. Power Query transformations are not a substitute for source-system access controls. Sensitive columns should not be imported merely because they are available. Privacy laws and organisational policy may require lawful purpose, restricted access, retention controls, and auditability.

Power Query privacy settings and the Formula.Firewall help control how sources are combined, but they do not eliminate the need for sound governance. Combining confidential data with public or externally supplied data requires explicit review.

11. Technology, AI, and Contemporary Practice

Modern Power BI environments increasingly use dataflows, cloud lakehouses, shared semantic models, deployment pipelines, and metadata-driven ingestion. These technologies can improve reuse and scale, but they do not remove the need to define grain, validate results, and assign ownership.

AI assistants can help draft M code, suggest transformations, explain errors, or generate documentation. Their output must be reviewed because generated code may use incorrect joins, mishandle privacy, or silently change business meaning. Do not paste confidential data into an unapproved AI service. Treat AI-generated transformations as untrusted code requiring testing, peer review, and documented approval.

A mature practice also uses automated data-quality checks, source-contract monitoring, version control where supported, and deployment separation between development, test, and production environments.

Challenges and Limitations

Power Query is highly capable, but it has limitations. Complex transformations may be difficult to maintain without modular design and documentation. Large volumes can create memory and refresh-time constraints. Query folding may not occur for every connector or transformation. Source systems may change without notice. Data-quality rules can identify problems but cannot always determine the correct business remedy.

Integration also involves organisational limitations. Different departments may disagree about definitions, ownership may be unclear, and source corrections may take longer than reporting deadlines. A responsible analyst should make uncertainty visible rather than manufacture false precision.

Best-Practice Checklist

Before releasing an integrated dataset, confirm that:

• the managerial purpose and grain are documented;
• each source has an owner and refresh expectation;
• types, locale, date logic, and currency logic are explicit;
• joins have been tested for cardinality and duplication;
• invalid and rejected records are visible;
• row counts and control totals reconcile;
• critical exceptions prevent misleading publication;
• parameters are used for environment-specific values;
• privacy, permissions, and retention have been reviewed;
• refresh monitoring and support ownership are defined;
• changes are documented and tested before deployment.

Chapter Summary

An applied Power Query project begins with a managerial information requirement and ends with a governed, refreshable data product. The analyst must define measures, dimensions, grain, timing, and acceptance criteria before transforming data. A layered architecture separates acquisition, staging, transformation, and outputs, improving reuse and lineage.

Reliable integration requires profiling for completeness, validity, consistency, uniqueness, timeliness, and conformity. Appends add compatible rows; merges add attributes through keys; both must be evaluated against table grain and relationship cardinality. Explicit types, controlled parameters, mapping tables, exception outputs, and documented business rules reduce operational risk.

Validation must include structural tests, row counts, key integrity, duplicate detection, reasonableness checks, and reconciliation to authoritative control totals. Refresh design, privacy, access, ownership, documentation, and change control are part of technical quality. The Northstar case demonstrates that integration errors often arise from incorrect grain and undocumented assumptions rather than from obvious query failures. A defensible Power BI solution therefore makes its definitions, transformations, exceptions, evidence, and limitations visible.

Key Terms

Append: Adds rows from two or more compatible tables.

Business rule: An agreed organisational condition or calculation applied to data.

Data lineage: The traceable path from a source field to its final use.

Data profiling: Examination of data structure, values, types, and quality.

Data quality: The fitness of data for its intended purpose.

Dimension: A descriptive structure used to classify or filter facts.

Exception table: A table containing records that fail or require review under a quality rule.

Fact table: A table containing measurable business events or observations.

Grain: The meaning represented by one row in a table.

Key: A field or combination of fields used to identify or relate records.

Merge: A Power Query operation that joins columns from tables using matching values.

Parameter: A named value used to make a query configurable.

Query folding: The translation of transformations into operations performed by the source system where supported.

Reconciliation: Comparison of an integrated result with an authoritative control value.

Reference query: A query that derives from another query while reusing its logic.

Refresh: Re-execution of data acquisition and transformation steps.

Staging layer: An intermediate layer that standardises and prepares source data.

Validation: Testing whether data and outputs satisfy defined requirements.

Review Questions

Short Answer Questions

1. What is meant by the grain of a table?
2. Distinguish between an append and a merge in Power Query.
3. Why should exception records be retained rather than silently deleted?
4. Name four dimensions of data quality.
5. What information should a refresh log contain?

Long Answer Questions

1. Explain how a managerial request can be converted into a formal data specification.
2. Describe a layered Power Query architecture and explain the purpose of each layer.
3. Discuss why explicit data types, locale, date handling, and currency definitions are important in multi-source integration.
4. Explain how reconciliation and row-count testing support confidence in an integrated dataset.
5. Evaluate the governance responsibilities of data owners, report owners, and technical owners.

Analytical / Application Questions

1. A target table contains one row per region-week and a sales table contains one row per invoice line. Explain the risk of joining them directly and design a safer comparison approach.
2. A folder ingestion query has refreshed successfully, but the output contains no transactions for the latest week. Identify possible causes and propose diagnostic tests.
3. A product master has two active rows for one product code. Explain how this can affect a merge, how you would prevent incorrect results, and what governance action should follow.

Multiple Choice Questions with Answers

Question 1

Which statement best defines table grain?

A. The number of columns in a table

B. What one row in the table represents

C. The refresh frequency of a table

D. The data type of the primary key

Correct Answer: B. What one row in the table represents

Question 2

Which Power Query operation is most appropriate for combining January, February, and March transaction rows with the same structure?

A. Merge

B. Append

C. Unpivot

D. Group By

Correct Answer: B. Append

Question 3

Why can joining region-week targets directly to invoice-line sales create overstated results?

A. Dates are automatically converted to text

B. Targets may be repeated for every invoice line

C. Append operations remove all nulls

D. Currency rates become zero

Correct Answer: B. Targets may be repeated for every invoice line

Question 4

Which test primarily checks whether a product code appears more than once in an active product master?

A. Completeness test

B. Timeliness test

C. Uniqueness test

D. Currency test

Correct Answer: C. Uniqueness test

Question 5

If one unit of source currency equals r units of reporting currency, which expression gives the reporting amount?

A. Source amount ÷ r

B. Source amount + r

C. Source amount × r

D. r − Source amount

Correct Answer: C. Source amount × r

Question 6

What is the principal purpose of a staging layer?

A. To display final dashboard visuals

B. To standardise and prepare source data

C. To replace all source-system controls

D. To store only rejected records

Correct Answer: B. To standardise and prepare source data

Question 7

Which practice provides the strongest evidence that an integrated monetary result is credible?

A. Changing all nulls to zero

B. Comparing it with an authoritative control total

C. Removing all rows with errors without recording them

D. Using automatic type detection only

Correct Answer: B. Comparing it with an authoritative control total

Question 8

What should happen to a source file that fails an expected-schema check in a governed solution?

A. It should always be silently ignored

B. It should be loaded as valid data

C. It should generate a visible warning or exception

D. Its columns should be randomly renamed

Correct Answer: C. It should generate a visible warning or exception

Question 9

Which item is most appropriate for a Power Query parameter?

A. A fixed row-level transaction description

B. A source folder path that differs between environments

C. A historical invoice identifier

D. A customer’s private password

Correct Answer: B. A source folder path that differs between environments

Question 10

Why must AI-generated M code be reviewed before production use?

A. AI code cannot contain any functions

B. Generated code may apply incorrect joins or business logic

C. Power Query does not support reviewed code

D. Review prevents all refreshes

Correct Answer: B. Generated code may apply incorrect joins or business logic