Data Acquisition and Preparation with Power Query

Introduction

Business intelligence depends on the quality, accessibility and meaning of data. Power BI can create sophisticated visualizations, but a dashboard cannot compensate for incomplete records, inconsistent definitions, duplicated transactions or incorrectly typed fields. Data acquisition and preparation therefore form a central part of the BI development process.

Power Query is Power BI’s data connection and transformation technology. It provides a graphical environment for extracting data from many sources, transforming it through repeatable steps, and loading the result into the Power BI data model. Power Query uses the M language internally, although most routine operations can be performed through menus.

The process is commonly described as ETL—extract, transform and load. In some modern architectures, transformation occurs before loading into a warehouse or lakehouse; this is often called ELT. Power Query can participate in either approach, depending on where transformations are performed and where the final analytical model is stored.

Learning Objectives

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

• connect Power BI to common files, databases, web services and organizational sources;
• distinguish import, DirectQuery and other connectivity approaches;
• profile data using column quality, distribution and profiling tools;
• clean and transform data using repeatable Power Query steps;
• combine data through append and merge operations;
• validate data types, keys, completeness and business rules;
• apply practical ETL/ELT governance and documentation practices.

1. Data Acquisition in the BI Lifecycle

Data acquisition is the controlled process of obtaining data from operational or external systems. Typical sources include Excel workbooks, CSV files, relational databases, cloud applications, SharePoint folders, data warehouses, APIs and web pages.

A useful preparation workflow is:

1. identify the business question and required data;
2. locate authoritative sources;
3. connect to the source;
4. inspect structure and quality;
5. transform data into an analytical shape;
6. validate results against source totals and business rules;
7. load the prepared tables into the model;
8. document and monitor the process.

This sequence is iterative rather than strictly linear. A profiling step may reveal that another source is required, while validation may require a transformation to be redesigned.

Common Power BI Connectivity Modes

Import generally offers the strongest interactive performance because the VertiPaq engine stores compressed data in memory. DirectQuery can provide fresher information, but performance depends on the source system, network and generated queries. The choice should be based on data volume, refresh requirements, source capability, security and report performance—not on a universal preference.

2. Connecting to Common Sources

In Power BI Desktop, the Get Data experience presents connectors grouped by source type. A connector does more than locate a file: it also determines how authentication, navigation, metadata and query execution are handled.

Files

Excel and CSV files are common departmental sources. When connecting to Excel, Power BI often displays available worksheets and tables. A defined Excel Table is usually preferable to an arbitrary cell range because it expands more reliably when rows are added. CSV files require attention to delimiter, encoding, quotation marks and regional settings.

A folder connector can combine files with a consistent structure. The files should normally have the same column meanings and compatible data types. The automatically generated sample-file transformation should be reviewed rather than accepted blindly.

Relational Databases

SQL Server and other relational connectors allow users to select tables or write native SQL queries. Database sources are often more stable than manually maintained spreadsheets, but table relationships and business definitions still require investigation. A database column named Status does not automatically reveal whether it means order status, payment status or shipment status.

Web and Cloud Sources

Web connectors may retrieve structured files, HTML tables or JSON data from an endpoint. APIs commonly require authentication, pagination and parameter handling. A successful connection does not prove that all records were retrieved. Analysts should examine response metadata, page counts, timestamps and error records.

3. Power Query’s Transformation Model

Power Query records transformations as an ordered sequence of applied steps. Each step takes the output of the preceding step as its input. This creates a visible data lineage and allows transformations to be refreshed when the source changes.

The underlying M language is functional and case-sensitive. A simplified query has the following conceptual structure:

let
    Source = ...,
    FilteredRows = Table.SelectRows(Source, each [Amount] > 0),
    Result = FilteredRows
in
    Result

The example is illustrative: Source represents an input table, FilteredRows retains records whose Amount is greater than zero, and Result is the value returned by the query. Users do not need to write M for every task, but understanding that each step produces a new result helps explain query behavior.

Query Folding

Query folding occurs when Power Query translates compatible transformations into operations executed by the source system. For example, a filter applied to a SQL table may become a SQL WHERE condition. Folding can reduce data transfer and improve refresh performance.

Folding depends on the connector, source capability and transformation sequence. Some operations—especially certain custom functions, complex text operations or actions performed after non-foldable steps—may prevent later steps from folding. The View Native Query option, where available, can help assess folding for relational sources. A non-folding transformation is not automatically wrong; it simply deserves performance consideration.

4. Profiling and Understanding Data

Profiling means examining data before making assumptions about it. Power Query provides column quality, column distribution and column profile views. These can reveal valid values, errors, empty values, distinct values and value frequencies.

Important profiling questions include:

• What percentage of rows is null or empty?
• Are values unique where uniqueness is expected?
• Are dates within the reporting period?
• Are numeric values plausible and in the correct unit?
• Do categories contain spelling or capitalization variants?
• Are there unexpected errors or mixed data types?
• Does the row count agree with the source system?

A data type is a semantic declaration, not merely a display format. A date stored as text cannot reliably support date filtering or time intelligence. A currency field stored as a whole number may silently lose decimal precision. Types should be explicitly checked for dates, integers, decimals, fixed decimals, text, Boolean values and identifiers.

Missing Values and Errors

A null indicates absence of a value; an empty text string is a text value containing no characters. These may have different business meanings. For example, a missing delivery date may mean “not yet delivered,” whereas an empty customer comment may mean “no comment supplied.” Replacing all missing values with zero can create false information.

Errors may result from invalid dates, incompatible types, malformed source records or failed custom logic. They should be investigated and classified. Removing errors may be appropriate for demonstrably corrupt rows, but it is poor practice when it merely hides a source-system problem.

5. Core Cleaning and Transformation Operations

Selecting, Removing and Renaming Columns

Remove columns that are irrelevant, duplicate, confidential or technically unnecessary. Prefer selecting required columns when source schemas are unstable, because a query that explicitly keeps needed columns may be more resilient than one that keeps every current column. Rename columns using clear, consistent business names, while preserving a mapping to source names where governance requires it.

Filtering Rows

Filters can remove test records, canceled transactions, invalid dates or irrelevant categories. Filtering must be documented because it changes the population represented by the report. A filter on Status = Completed is not equivalent to filtering out null statuses; the latter may conceal incomplete records.

Replacing Values and Standardizing Categories

Text values may differ because of capitalization, spelling, punctuation or trailing spaces. Common preparation actions include trimming spaces, cleaning non-printing characters, standardizing case and replacing known variants. Transformations should avoid altering legitimate distinctions. For example, “US” and “U.S.” may represent the same country, but “East” and “East Coast” may not.

Splitting, Merging and Extracting Text

Text can be split by delimiter, character position or transition from lower to upper case. Columns can also be combined to create labels or keys. Care is required with names, addresses and free text because delimiters may occur inside valid values.

Dates and Numbers

Dates should be interpreted using the correct locale. A value such as 03/04/2025 may mean 3 April or March 4 depending on regional convention. Number conversion similarly depends on decimal and thousands separators. Locale-aware conversion is preferable to assuming that the displayed format is universal.

Derived columns can be created for year, month, quarter, age bands or business classifications. However, reusable calendar attributes are often better managed in a dedicated date table within the model.

Grouping and Aggregation

Grouping summarizes records by one or more keys, such as total sales by customer. It is appropriate when the analytical grain is intentionally changed. If transaction-level detail is required later, premature grouping destroys information. Analysts should identify the grain—the level represented by one row—before aggregating.

6. Combining Data: Append and Merge

Appending adds rows from two or more tables. It is appropriate when tables have the same conceptual structure, such as monthly sales files. Columns are matched by name, and missing columns produce null values. Before appending, confirm that the tables represent compatible entities and that their column meanings and units agree.

Merging joins columns from one table to another using one or more matching fields. It is appropriate when a transaction table must be enriched with customer, product or regional attributes. A merge requires an explicit join kind, such as left outer, inner or full outer.

A left outer merge retains every row from the primary table and matching rows from the secondary table. If the secondary key is not unique, expansion can duplicate primary rows. Therefore, key uniqueness must be tested before expanding a merged table.

7. Validation and Data Quality Controls

Validation determines whether prepared data is fit for its intended use. It should occur at multiple levels.

Structural Validation

Check that expected tables, columns and data types exist. A missing column may indicate a changed source schema rather than an ordinary data-quality issue.

Completeness Validation

Compare expected and actual row counts, date coverage and key-field population. A completeness rate can be expressed as:

Here, “Non-null required values” is the count of populated values in a required field, and “Total required values” is the number of records evaluated. The measure applies to a clearly defined field and population; it does not prove that populated values are correct.

For example, if 9,500 of 10,000 customer records contain a customer identifier, completeness is (9,500 ÷ 10,000) × 100% = 95%.

Uniqueness and Referential Validation

A key intended to identify records should not contain unexpected duplicates. A product code in a sales table should match a valid product code in the product dimension. Unmatched keys should be reported and investigated rather than silently discarded.

Reconciliation

Reconcile prepared data with trusted source totals. For example, compare transaction count and total invoiced amount by month. Differences may be legitimate because of filtering or timing, but they must be explainable.

8. A Repeatable ETL/ELT Design Pattern

A maintainable Power Query solution commonly separates queries into layers:

1. Source or staging queries connect to raw data and apply only necessary technical preparation.
2. Transformation queries clean, standardize and combine data.
3. Reference or dimension queries provide reusable lookup structures.
4. Output queries load only tables required by the model.

Use meaningful query names, group related queries, disable load for staging queries where appropriate, and document assumptions. Parameters can store file paths, server names, reporting dates or environment settings. This reduces hard-coded values and supports movement from development to production environments.

A transformation should be deterministic where possible: the same source state and parameters should produce the same output. Avoid manually editing individual cells because such edits are difficult to reproduce during refresh.

9. Applied Case: Regional Retail Sales

The following is an illustrative case.

Background

A retail company receives monthly CSV files from four regions. Each file contains transaction date, product code, sales amount, tax amount and region. Product descriptions are maintained in a separate Excel workbook.

Problem

The files use different date formats, one region labels sales as North with a trailing space, and the product workbook contains duplicate product codes. Management reports have inconsistent monthly totals because some files contain repeated headers and canceled transactions.

Analysis

The analyst profiles each file, checks row counts and inspects distinct values. The duplicate product codes show that the product workbook is not a reliable one-row-per-product lookup. The repeated headers fail numeric conversion, while canceled transactions are identifiable through a status field.

Approach and Solution

The analyst creates a folder query for the regional files and standardizes the schema before appending them. Repeated header rows are removed, dates are converted using the correct locale, region values are trimmed, and canceled transactions are excluded according to the agreed reporting definition. A separate product query removes exact duplicate records only after confirming that duplicates contain identical attributes. Conflicting product records are sent back to the data owner rather than resolved arbitrarily.

The sales query is merged with the validated product table using product code. Unmatched product codes are retained for an exception report. Monthly transaction counts and sales totals are reconciled against the finance system.

Outcome

The resulting model refreshes from the folder without manual file editing. Regional comparisons become consistent, exceptions are visible, and the business receives a documented definition of reported sales.

Learning Points

The case demonstrates that preparation is not simply formatting. It involves source assessment, business-rule interpretation, key validation, controlled exception handling and reconciliation. Automation improves repeatability only when the underlying rules are explicit.

10. Governance, Security and Contemporary Practice

Data preparation may expose personally identifiable information, financial records or commercially sensitive data. Analysts should apply least-privilege access, avoid copying unnecessary personal fields, protect credentials and follow organizational retention and privacy policies. Power Query transformations do not replace source-level security or Power BI row-level security.

Refresh credentials, gateways and privacy-level settings must be managed appropriately. Combining sources with different privacy classifications can affect how Power Query evaluates data movement. Organizational governance should define approved connectors, naming conventions, ownership, testing and change control.

AI-assisted features can help suggest transformations or explain errors, but they do not establish data accuracy or business meaning. Human review remains necessary, especially when transformations affect financial, regulatory or customer decisions. Increasingly, organizations use data catalogs, lineage tools, data-quality rules and lakehouse architectures to make preparation more observable and scalable.

Challenges and Limitations

Power Query is highly useful, but it is not a substitute for a well-designed data architecture. Large-scale transformations may be more efficient in a warehouse or lakehouse. DirectQuery performance can suffer when source indexes, joins or network capacity are inadequate. Schema changes can break queries, and poorly designed custom functions can increase refresh time. Automatic type detection may be convenient but should not be treated as authoritative. Finally, a technically successful refresh can still produce a misleading dashboard if business definitions are wrong.

Best Practices

• Start with the reporting question and define the required grain.
• Use authoritative sources and record ownership.
• Profile before transforming.
• Set data types explicitly and consider locale.
• Prefer repeatable steps over manual edits.
• Preserve exceptions for investigation.
• Validate keys before merging.
• Reconcile row counts and important totals.
• Keep staging, transformation and output logic understandable.
• Use parameters and avoid hard-coded environment-specific paths.
• Review query folding and refresh performance.
• Protect sensitive data and document assumptions.

Chapter Summary

Power Query provides a structured environment for acquiring, profiling, cleaning, transforming, combining and validating data for Power BI. Import, DirectQuery and composite approaches offer different trade-offs in freshness, performance and governance. Reliable preparation requires explicit data types, careful treatment of nulls and errors, correct handling of locale, and awareness of the grain of each table. Append operations add compatible rows, whereas merge operations add columns through keys; both require semantic and structural validation. Query folding can improve performance by moving compatible work to the source. A professional ETL/ELT process is repeatable, documented, secure and reconciled against trusted totals. Technology can automate transformation, but business definitions and quality judgments remain human responsibilities.

Key Terms

Data acquisition: The controlled process of obtaining data from one or more sources.

Power Query: Microsoft’s technology for connecting to and transforming data in Power BI and related products.

ETL: Extract, transform and load.

ELT: Extract, load and transform.

Import mode: A connectivity mode in which data is loaded into the Power BI model.

DirectQuery: A mode that sends queries to the underlying source during report use.

Query folding: Translation of Power Query operations into source-system operations.

Data profiling: Systematic inspection of structure, completeness, distribution and errors.

Data type: A declaration of the kind of value stored in a field.

Null: An absent or unknown value.

Data grain: The level represented by one row in a table.

Append: Combining tables by adding rows.

Merge: Combining tables by matching keys and adding columns.

Primary key: A field or field combination intended to uniquely identify records.

Referential integrity: The condition that related keys correspond to valid records.

Locale: Regional conventions governing formats such as dates and numbers.

Parameter: A reusable value that controls a query or transformation.

Staging query: A preliminary query used to access or technically prepare source data.

Reconciliation: Comparison of prepared results with trusted source results.

Query folding: The process of pushing compatible transformations back to the source system.

Review Questions

Short Answer Questions

1. What is the difference between Import mode and DirectQuery?
2. Why should data be profiled before transformation?
3. What is the difference between appending and merging tables?
4. Why is explicit data typing important in Power Query?
5. What does query folding mean?

Long Answer Questions

1. Explain a complete Power Query workflow from source selection through validation.
2. Discuss the risks of replacing nulls and errors without investigating their meanings.
3. Explain how query grain affects grouping and aggregation decisions.
4. Describe the governance practices required for secure and maintainable Power Query solutions.
5. Compare the advantages and limitations of file-based sources and relational database sources.

Analytical / Application Questions

1. A folder contains twelve monthly files with inconsistent date formats and occasional repeated headers. Design a preparation and validation approach.
2. A merge increases a sales table from 100,000 to 128,000 rows. What possible key problem does this indicate, and how would you investigate it?
3. A report total is 4% lower than the finance system after transformation. Develop a reconciliation plan to determine whether the difference is caused by filtering, duplication, missing records or timing.

Multiple Choice Questions with Answers

Question 1

Which Power Query operation adds rows from tables with compatible structures?

A. Merge

B. Append

C. Transpose

D. Pivot

Correct Answer: B. Append

Question 2

What is the main purpose of data profiling?

A. To create dashboard themes

B. To inspect quality, structure and distributions before transformation

C. To replace the semantic model

D. To publish a report

Correct Answer: B. To inspect quality, structure and distributions before transformation

Question 3

Which connectivity mode generally stores data in the Power BI model?

A. Import

B. DirectQuery

C. Live web query only

D. Gateway mode

Correct Answer: A. Import

Question 4

What can happen when the lookup key in a merge is not unique?

A. Primary rows may be duplicated after expansion

B. All nulls become zeros

C. The source database is deleted

D. The report automatically changes to DirectQuery

Correct Answer: A. Primary rows may be duplicated after expansion

Question 5

Which statement correctly distinguishes null from an empty text string?

A. They always have identical business meaning

B. A null represents absence, while an empty string is a text value with no characters

C. An empty string can only occur in numeric fields

D. Null is always equal to zero

Correct Answer: B. A null represents absence, while an empty string is a text value with no characters

Question 6

If 9,500 of 10,000 required values are non-null, what is the completeness rate?

A. 5%

B. 90%

C. 95%

D. 105%

Correct Answer: C. 95%

Question 7

Which practice most directly supports repeatable refreshes?

A. Manually editing individual cells

B. Using recorded transformation steps and parameters

C. Deleting source files after import

D. Changing values in the report visual

Correct Answer: B. Using recorded transformation steps and parameters

Question 8

What does query folding do when supported by the source?

A. Pushes compatible transformations to the source system

B. Converts every column to text

C. Removes all duplicate rows automatically

D. Publishes the report to the service

Correct Answer: A. Pushes compatible transformations to the source system

Question 9

Why should locale be considered when converting dates?

A. Locales determine dashboard color palettes

B. The same displayed date can be interpreted differently across regional conventions

C. Locale controls the number of report pages

D. Locale determines whether a table has a primary key

Correct Answer: B. The same displayed date can be interpreted differently across regional conventions

Question 10

Which validation is most appropriate after merging sales with a product table?

A. Check unmatched product codes and whether the product key is unique

B. Change all product codes to dates

C. Remove every unmatched sale without review

D. Hide the product table from all users

Correct Answer: A. Check unmatched product codes and whether the product key is unique