Data Sources, Connectors and Access Modes

Introduction

Business intelligence depends not only on the quality of a report but also on the reliability of the path by which data reaches the report. Power BI and Power Query provide connectors that establish this path. A connector identifies a source type, negotiates access, retrieves data, and exposes transformation capabilities. The choice of connector and access mode influences refresh performance, security, modelling flexibility, governance, and the freshness of analysis.

A professional Power BI developer must therefore ask more than “Can Power BI connect to this source?” The more important questions are: What exactly is being connected? Which credentials are required? Where will the query execute? How frequently can data be refreshed? What privacy restrictions apply? Will the connection support the required transformations and relationships? What happens if the source is unavailable?

This chapter examines files, folders, databases, web APIs, cloud services, and organizational dataflows. It also evaluates authentication, privacy levels, Import and DirectQuery, composite models, gateways, and common connection limitations.

Learning Objectives

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

• distinguish between major Power Query source categories and connector functions;
• connect responsibly to files, folders, databases, APIs, cloud platforms, and dataflows;
• select appropriate authentication methods and understand credential scope;
• explain privacy levels and the Formula Firewall;
• compare Import, DirectQuery, and composite models;
• identify gateway, refresh, query-folding, licensing, and performance limitations;
• design a source strategy that balances freshness, security, maintainability, and analytical flexibility.

1. The Power Query Connection Model

Power Query is an extract, transform, and load technology. A query generally performs four conceptual activities:

1. Connection: locating and opening a source.
2. Navigation: selecting a table, sheet, endpoint, file, or object.
3. Transformation: cleaning, reshaping, combining, and filtering data.
4. Loading: placing the result into a Power BI semantic model or another destination.

A connector is not merely a file-picker or login screen. It contains source-specific logic. For example, a SQL connector can expose databases and tables, support query folding, and use database authentication, whereas a CSV connector interprets delimiters, encoding, and data types.

The source can be local, on a private network, or in the cloud. Its location affects refresh architecture. Power BI Service can usually reach cloud sources directly, but a private SQL Server or an on-premises file share normally requires an on-premises data gateway.

Figure: The conceptual path from a source to a Power BI report.

2. Major Source Categories

2.1 Files and folders

Power Query can connect to Excel workbooks, CSV and text files, XML, JSON, Parquet, PDFs, and other supported file types. File connections are useful for departmental extracts, scheduled exports, and small-to-medium operational datasets.

An Excel connection should normally target a structured table or named range rather than an arbitrary cell region. Tables provide stable names and boundaries. CSV connections require attention to delimiter, encoding, quotation marks, header promotion, and data types. JSON may contain nested records and lists that must be expanded carefully.

The Folder connector is valuable when many files share a common structure. It returns file metadata and binary content, after which a transformation function can apply the same logic to each file. The files should have consistent columns, compatible data types, and a predictable naming convention. A robust design filters out temporary files, hidden files, and unrelated documents before combining them.

File limitations include changing column layouts, locked or partially written files, duplicate records, inconsistent locale settings, and dependence on a user’s personal folder. A published dataset cannot reliably refresh from a laptop path unless the service can reach that path through an appropriate gateway.

2.2 Relational and analytical databases

Connectors exist for SQL Server, PostgreSQL, Oracle, MySQL, Snowflake, BigQuery, Dataverse, SAP, and many other systems. Database connections are generally preferable to manual exports because they can use indexes, views, permissions, incremental extraction, and query folding.

A connection may use a server and database name, an SQL statement, or a navigated table or view. A view can centralize business logic and security, while a native SQL query can be precise but may reduce portability and folding opportunities. Developers should avoid importing unnecessary columns and rows at the source.

Database connectivity introduces issues such as network latency, drivers, version compatibility, firewall rules, concurrent workload, and server permissions. A query that works in Power BI Desktop may fail in the Power BI Service if the service lacks a gateway or the gateway machine lacks the required driver.

2.3 Web APIs and web pages

The Web connector can retrieve content from URLs, including JSON, XML, CSV, and HTML. APIs usually require parameters, headers, tokens, pagination, rate-limit handling, and error handling. A production API query should not assume that all records are returned in one response.

Common API authentication methods include anonymous access, API keys, basic authentication, OAuth 2.0, and organizational accounts. Tokens and keys must not be hard-coded in M code or stored in shared workbooks. API contracts should be reviewed for data ownership, permitted use, retention, and rate limits.

Web pages are less stable than formal APIs. HTML structures can change without notice, and scraping may breach terms of use. When an official API or downloadable dataset exists, it is generally preferable to scraping a visual web page.

2.4 Cloud services

Cloud connectors support services such as SharePoint Online, OneDrive for Business, Azure services, Salesforce, Google platforms, Dataverse, and other software-as-a-service products. These connectors often provide organizational authentication, metadata discovery, and service-specific navigation.

Cloud location does not automatically mean that every user can access the data. Access is governed by the identity used by the connector, tenant permissions, workspace roles, row-level security, and the service’s licensing model. A developer should distinguish between a personal connection and a reusable organizational connection.

2.5 Dataflows and organizational data products

A dataflow is a cloud-managed collection of Power Query transformations whose outputs can be consumed by reports, semantic models, or other data processes. Dataflows can centralize cleansing and reduce duplication across many reports. They are especially useful when multiple teams require the same definitions of customers, products, or calendar data.

Dataflows do not remove the need for governance. Owners must document refresh schedules, dependencies, data quality rules, credentials, lineage, and business definitions. A report connected to a dataflow may be easier to maintain, but it can also become dependent on a centrally managed asset that changes without adequate communication.

3. Credentials, Authentication and Authorization

Authentication answers “Who are you?” Authorization answers “What are you allowed to access?” Power Query stores data-source credentials and uses them when evaluating a source. Credential scope matters: a credential can be associated with a particular URL, server, database, path, or cloud service.

Typical methods include:

Credentials should be managed through approved organizational processes. Developers should use read-only accounts where possible, avoid embedding secrets in query text, rotate keys, and remove obsolete permissions. A refresh identity must be tested separately from the developer’s interactive identity.

A gateway acts as an intermediary between Power BI Service and sources that the service cannot reach directly. The gateway must be online, correctly configured, associated with the dataset or dataflow, and able to authenticate to the source. For resilient operations, organizations may use a gateway cluster rather than a single machine.

4. Privacy Levels and Data Combination

Power Query privacy levels help control how data from different sources may be combined. The principal classifications are:

• Public: the source contains information that may be freely exposed;
• Organizational: the source is restricted to an organization;
• Private: the source contains highly restricted or personal information.

Privacy settings are intended to prevent unintended disclosure when queries combine sources. For example, a private customer list should not automatically be used to generate requests sent to a public web service. Power Query may block or isolate such combinations through the Formula Firewall.

Privacy levels are not a replacement for database security, encryption, data loss prevention, or legal compliance. They are query-evaluation protections. An organization may disable privacy checks in controlled environments for performance or compatibility, but this should be a documented risk decision rather than a casual troubleshooting step.

When a privacy error occurs, the correct response is to identify the sources, confirm their classifications, review the intended data flow, and redesign the query if necessary. Simply ignoring the warning can create a data-leakage risk.

5. Import, DirectQuery and Composite Models

Import mode

Import mode copies data into the Power BI semantic model. Reports query the in-memory model rather than repeatedly querying the original source. This usually provides strong interactive performance and broad modelling and transformation capabilities. Data is updated during refresh, so it is not automatically identical to the source between refresh events.

Import is suitable when data volume is manageable, scheduled freshness is acceptable, and analytical performance is important. It requires storage capacity and a refresh plan.

DirectQuery mode

DirectQuery stores metadata and sends queries to the source when users interact with reports. It can reduce the need to import very large datasets and can provide more current results. However, performance depends on the source, network, generated queries, concurrency, indexes, and gateway path. Some Power Query transformations and modelling features are restricted or behave differently.

DirectQuery is not automatically real-time. Source latency, caching, service limitations, and refresh of metadata still matter. It should be chosen after testing representative report interactions against realistic workloads.

Composite models

Composite models combine Import and DirectQuery tables, and may also use dual storage mode. They can place frequently filtered dimensions in Import while leaving very large fact data in DirectQuery. This flexibility introduces complexity: relationships, cross-source behaviour, security, aggregation design, and performance must be tested carefully.

The selection should be based on business requirements rather than fashion. A simple decision rule is: use Import when refresh latency is acceptable and performance matters; consider DirectQuery when data volume or freshness makes Import unsuitable; use composite architecture only when its additional complexity is justified.

6. Query Folding and Connection Performance

Query folding occurs when Power Query translates transformation steps into operations performed by the source system. Filtering a SQL table early may become a server-side WHERE operation, reducing data transferred to Power BI. Folding depends on the connector, source capabilities, transformation sequence, and query design; it is not guaranteed for every step or source.

Good practice includes selecting required columns early, filtering rows early, using typed and indexed source fields, avoiding unnecessary row-by-row custom functions, and checking whether folding remains available. A transformation that stops folding may cause large amounts of data to be downloaded and processed locally.

Performance must be assessed end-to-end. Relevant measures include refresh duration, source CPU, data transferred, gateway capacity, model size, report visual response time, and concurrent-user behaviour. A fast Desktop test with a small sample is not evidence of production readiness.

7. Connection Limitations and Failure Modes

Common limitations include:

• unsupported data types or connector features;
• API pagination and rate limits;
• expired tokens and changed passwords;
• gateway downtime or unavailable drivers;
• firewall, DNS, proxy, and certificate problems;
• source schema changes;
• privacy conflicts when combining sources;
• refresh-duration and storage-capacity limits;
• DirectQuery timeouts and source contention;
• inconsistent regional settings for dates, numbers, and delimiters;
• licensing or tenant restrictions;
• ambiguous ownership of personal files and cloud assets.

Error handling should distinguish transient failures from structural failures. A retry may help with a temporary network error, but it will not fix a renamed column or revoked permission. Production solutions should include monitoring, refresh alerts, documented ownership, source contracts, and a tested recovery procedure.

8. Applied Case: Regional Retail Performance Model

Background

The fictional Meridian Retail Group operates stores in three regions. Sales transactions are held in an Azure-hosted SQL database, product information is maintained in a SharePoint Excel workbook, and regional managers submit monthly adjustment files to a shared folder. Executives require daily reporting, while finance requires controlled definitions and an audit trail.

Problem

The initial report imported a full SQL table, combined Excel files manually, and used each analyst’s personal credentials. Refreshes were slow, regional files had inconsistent columns, and a product workbook occasionally exposed discontinued products. The report worked on the author’s computer but failed in the service.

Analysis

The SQL database was suitable for a managed connection and query folding. The SharePoint workbook required organizational authentication and ownership assignment. The folder needed a standard template and validation of file names and columns. Because the database was cloud-hosted, a gateway was unnecessary for that source; however, the organization had to confirm network and tenant configuration. The model required daily freshness, not second-by-second reporting, so Import was appropriate.

Approach and solution

The team created a read-only database account and a certified database view containing only approved sales fields. Power Query filtered the required date range and selected columns before loading. A folder query excluded temporary files and applied a validation function that rejected files missing required columns. The product workbook was moved to a controlled SharePoint location and connected with an organizational account.

Common cleansing logic was placed in an organizational dataflow. The semantic model imported the curated outputs and refreshed after the dataflow. Privacy levels were classified as Organizational, and the team documented why no private source was combined with a public endpoint. A gateway was configured only for a remaining on-premises finance source, with a backup gateway member.

Outcome

Refresh became predictable, credentials were no longer tied to one employee, and report performance improved because filtering and column selection occurred near the source. The organization also gained a documented lineage from source systems to the executive report.

Learning points

The case illustrates that connector selection is an architectural decision. A successful design aligns source ownership, authentication, privacy, transformation location, refresh frequency, and access mode. Technical connectivity alone does not establish a reliable BI solution.

9. Governance, Ethics and Contemporary Practice

Data connections may expose personal, confidential, or commercially sensitive information. Designers must apply data minimization, purpose limitation, access control, retention rules, and auditability. Personal data should not be copied into a model merely because a connector makes it technically possible. Row-level security and workspace permissions should be tested using representative user identities.

AI-assisted development can suggest Power Query steps or M expressions, but generated code must be reviewed. It may mishandle credentials, privacy boundaries, data types, pagination, or business definitions. AI should assist development, not replace source-owner approval, security review, and testing.

Contemporary practice increasingly emphasizes reusable data products, semantic model governance, lineage, incremental refresh, deployment pipelines, and centralized monitoring. These practices reduce duplicated logic but increase the need for change management and clear ownership.

Best-Practice Checklist

Before publishing a connection, confirm that:

1. the source owner and business purpose are documented;
2. the connector is appropriate for the source and required transformations;
3. credentials use least privilege and are not embedded in code;
4. privacy levels reflect actual sensitivity;
5. gateway, driver, firewall, and network requirements are tested;
6. query folding or source-side filtering is used where beneficial;
7. refresh frequency matches the business requirement;
8. schema changes and API limits have been considered;
9. Import, DirectQuery, or composite mode has been selected through testing;
10. monitoring, failure notification, lineage, and recovery procedures exist.

Chapter Summary

Power Query connectors provide structured access to files, folders, databases, APIs, cloud services, and organizational dataflows. Connection design involves more than selecting a source: it includes navigation, transformation, credentials, privacy, gateways, refresh, and governance. Import generally offers strong performance and modelling flexibility, while DirectQuery reduces imported storage but makes report performance dependent on the source and network. Composite models offer selective flexibility at the cost of additional complexity. Query folding, source-side filtering, least-privilege credentials, correct privacy classification, and operational monitoring are central to professional practice. A reliable Power BI solution is therefore an engineered data pathway rather than an isolated report feature.

Key Terms

Connector: A component that enables Power Query to access a particular source type.

Query folding: Translation of Power Query transformations into operations executed by the source.

Import mode: Access mode in which data is copied into the Power BI semantic model.

DirectQuery: Access mode in which report queries are sent to the source during interaction.

Composite model: A model combining storage modes or multiple source behaviours.

Gateway: A service that relays communication between Power BI Service and reachable private sources.

Credential: Information used to authenticate a connection.

Authentication: Verification of an identity.

Authorization: Determination of permitted actions or data access.

Privacy level: A classification controlling how Power Query may combine source data.

Formula Firewall: Power Query mechanism that helps prevent unsafe data combination.

Dataflow: A cloud-managed set of Power Query transformations and reusable outputs.

API: Application Programming Interface used for structured software-to-software access.

Pagination: Retrieval of a large API result through multiple responses or pages.

Refresh: Process of updating a model or dataflow from its sources.

Schema: The structural definition of fields, tables, types, and relationships.

Least privilege: Granting only the permissions required for a task.

Semantic model: The governed analytical data model used by Power BI reports.

Row-level security: Rules restricting the rows visible to particular users.

Review Questions

Short Answer Questions

1. What are the four conceptual activities in a Power Query connection process?
2. Why is a folder connector useful when many files share a structure?
3. Distinguish authentication from authorization.
4. What is query folding?
5. State one major advantage and one limitation of Import mode.

Long Answer Questions

1. Explain how files, databases, APIs, cloud services, and dataflows differ as Power BI sources.
2. Compare Import, DirectQuery, and composite models, including suitable use cases.
3. Explain privacy levels and the Formula Firewall, and discuss why they do not replace security controls.
4. Describe the role of a gateway in scheduled refresh and identify factors that can cause gateway failure.
5. Develop a governance policy for credentials, source ownership, refresh monitoring, and schema changes.

Analytical / Application Questions

1. A report connects successfully on a developer’s laptop but fails during Power BI Service refresh. Diagnose at least four possible causes and propose a testing sequence.
2. An organization wants near-current reporting from a very large SQL fact table but has limited database capacity. Evaluate Import, DirectQuery, and a composite design, stating the evidence required before choosing.
3. A query combines a private customer file with a public web API. Explain the privacy and ethical risks and design a safer alternative.

Multiple Choice Questions with Answers

Question 1

Which Power Query connector is most appropriate for combining many files with a common structure?

A. Folder

B. Web page

C. Blank query only

D. PDF table

Correct Answer: A. Folder

Question 2

What does authentication establish?

A. Which rows a report displays

B. The identity attempting access

C. Whether a query folds

D. The refresh frequency

Correct Answer: B. The identity attempting access

Question 3

Which access mode stores data in the Power BI semantic model?

A. DirectQuery

B. Live connection only

C. Import

D. Anonymous mode

Correct Answer: C. Import

Question 4

What is the main purpose of query folding?

A. To encrypt all report pages

B. To translate transformations for execution by the source

C. To assign workspace roles

D. To combine unrelated files automatically

Correct Answer: B. To translate transformations for execution by the source

Question 5

Which privacy classification normally indicates highly restricted information?

A. Public

B. Organizational

C. Private

D. Anonymous

Correct Answer: C. Private

Question 6

Why might a gateway be required?

A. To connect Power BI Service to a source on a private network

B. To convert every query into DirectQuery

C. To replace database permissions

D. To remove all refresh limits

Correct Answer: A. To connect Power BI Service to a source on a private network

Question 7

Which statement best describes DirectQuery?

A. All source data is permanently copied during report design

B. Report interactions can generate queries against the source

C. It can use only CSV files

D. It eliminates source performance concerns

Correct Answer: B. Report interactions can generate queries against the source

Question 8

Which practice best protects a production data connection?

A. Embedding passwords in M code

B. Using a shared administrator account

C. Using least-privilege credentials and documenting ownership

D. Disabling all privacy checks by default

Correct Answer: C. Using least-privilege credentials and documenting ownership

Question 9

What is a principal benefit of an organizational dataflow?

A. It prevents the need for any source permissions

B. It centralizes reusable transformation logic

C. It guarantees real-time reporting

D. It removes the need for data quality checks

Correct Answer: B. It centralizes reusable transformation logic

Question 10

Why can web APIs require special query design?

A. They always contain relational tables

B. They may impose pagination, tokens, and rate limits

C. They cannot return JSON

D. They never change their schemas

Correct Answer: B. They may impose pagination, tokens, and rate limits