Data Sources and Connectivity Modes

Introduction

Business intelligence begins with a connection to trustworthy data. In Power BI, a report is only as reliable as the sources from which it obtains data, the transformations applied to those data, and the connectivity mode used to query or store them. A data source may be a workbook on a local computer, a collection of CSV files, a relational database, a webpage, an application programming interface (API), SharePoint, or a cloud platform. Each source has different characteristics concerning structure, refresh, security, performance, and ownership.

Power BI provides two closely related capabilities. Power BI Desktop enables analysts to connect to sources, transform data with Power Query, build models, and design reports. The Power BI service enables publication, sharing, scheduled refresh, gateways, semantic-model management, and cloud connectivity. A sound solution therefore requires more than selecting a connector: it requires an appropriate architecture and connectivity mode.

This chapter explains the main source categories, connection procedures, and the differences among Import, DirectQuery, live connection, and composite models.

Learning Objectives

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

• identify suitable Power BI connectors for common business sources;
• connect to Excel, CSV, folders, databases, webpages, APIs, SharePoint, cloud services, and dataflows;
• explain how Power Query discovers, profiles, and transforms source data;
• distinguish Import, DirectQuery, live connection, and composite approaches;
• evaluate sources according to refresh, performance, security, governance, and maintainability;
• select a practical connectivity strategy for a business scenario.

1. The Data-Connection Lifecycle

A Power BI data connection normally follows this sequence:

1. Identify the source. Establish where the data resides, who owns it, how often it changes, and whether it is structured.
2. Select a connector. Choose the native connector when one is available rather than relying on a less controlled workaround.
3. Authenticate. Supply credentials using an appropriate method, such as organizational account, database credentials, Windows authentication, or an API key.
4. Select objects. In a database, these may be tables, views, or stored procedures; in a workbook, they may be sheets or named tables.
5. Transform with Power Query. Remove unnecessary columns, correct data types, standardize names, filter rows, and combine data where appropriate.
6. Load or connect. Data may be imported into the Power BI model or queried at report time.
7. Model and report. Relationships, measures, hierarchies, and visualizations are created after the source has been prepared.
8. Publish and operate. Configure refresh, gateway connections, permissions, monitoring, and change management.

Power Query records transformations as ordered steps. A transformation should be reproducible: another refresh should apply the same logic to newly arriving data. This is one reason that manual editing of source files is usually inferior to documented query steps.

A useful distinction is between a data source, a connection, and a semantic model. The source is the original system; the connection defines how Power BI reaches it; and the semantic model contains tables, relationships, calculations, and metadata used by reports.

2. Common Source Types and Their Connectors

2.1 Excel Workbooks

Excel is common in departmental reporting because it is accessible and flexible. The Excel connector can expose worksheets, Excel tables, and named ranges. An Excel table is generally preferable to an arbitrary worksheet range because it has explicit column headers and expands more predictably as rows are added.

Good practice includes:

• converting source ranges to Excel tables;
• giving tables and columns meaningful names;
• separating data from presentation sheets;
• avoiding merged cells, subtotals, repeated headers, and decorative titles in data tables;
• storing dates as dates and numeric values as numbers;
• keeping a stable file path or controlled SharePoint location.

A workbook may contain formulas whose displayed values are loaded, but Power BI is not a replacement for Excel's calculation engine. Complex workbook logic should be documented and, where feasible, moved into Power Query, the semantic model, or a governed source system.

2.2 CSV and Text Files

CSV files are simple and portable, but they are not fully self-describing. A connection may depend on delimiter, encoding, quotation marks, decimal conventions, and whether the first row contains headers. A comma in a text field must be correctly enclosed in quotation marks; otherwise columns may be split incorrectly.

Before loading a CSV, inspect:

• delimiter and text encoding;
• header-row position;
• missing-value conventions;
• date and decimal formats;
• duplicate column names;
• unexpected footers or explanatory rows.

Automatic data-type detection is convenient but should be reviewed. For example, an identifier such as 001245 may be incorrectly converted to a number, losing leading zeros. Identifiers are usually text even when they contain only digits.

2.3 Folders and Files in Bulk

The Folder connector is useful when a business receives periodic files with the same structure, such as daily sales extracts. Power Query can filter the file list, exclude temporary files, invoke a transformation for each file, and combine the results.

The pattern depends on a stable file contract:

• consistent column meanings;
• compatible data types;
• predictable headers;
• controlled file naming;
• a defined rule for duplicate or late-arriving files.

A folder query should filter before combining. For example, it may retain only .csv files whose names begin with Sales_ and exclude hidden files. If a new file has different columns, the combine operation may fail or produce incomplete data. Folder ingestion is therefore an operational process, not merely a technical shortcut.

2.4 Relational Databases

Power BI includes connectors for systems such as SQL Server, Azure SQL Database, Oracle, MySQL, PostgreSQL, and other database technologies. Database connections may expose tables, views, and sometimes native queries or stored procedures.

A database view can provide a controlled interface that hides implementation detail and limits access to required columns. However, a view may also conceal inefficient joins or prevent effective query folding. The appropriate choice depends on the source team's design and governance.

Database connections require attention to:

• server and database names;
• authentication method;
• network access and firewall rules;
• permissions on schemas, tables, and views;
• query folding;
• gateway requirements for private networks;
• workload impact on the operational database.

Query folding occurs when Power Query translates compatible transformation steps into a query executed by the source system. Filtering rows at the database is normally more efficient than retrieving all rows and filtering locally. Folding is connector- and transformation-dependent; it should be checked rather than assumed.

2.5 Webpages

The Web connector can retrieve tabular data from webpages and, in some circumstances, structured content embedded in pages. A webpage designed for human presentation is not necessarily a stable data source. Layout changes, pagination, client-side rendering, bot controls, and inconsistent HTML can break a query.

A published download file or an official API is usually preferable to scraping a page. If a webpage must be used, document its URL, expected structure, refresh frequency, licensing conditions, and failure response.

2.6 APIs

An API provides programmatic access to an application or service. Responses are commonly JSON or XML and may be nested rather than tabular. Power Query can expand records and lists into columns and rows, but API ingestion requires careful attention to:

• authentication and secret storage;
• endpoint parameters;
• pagination;
• rate limits;
• status codes and error responses;
• incremental retrieval using dates or change tokens;
• schema changes;
• privacy and licensing terms.

An API key should not be embedded in a publicly shared query or report. Organizational credentials, secure parameters, or an intermediary service should be used where appropriate. APIs may return only a fixed page of records, so failing to implement pagination can silently produce incomplete results.

2.7 SharePoint and OneDrive

SharePoint Online and OneDrive for Business are common sources for files, lists, and document libraries. A SharePoint Folder connection is useful when multiple files are managed in a document library. SharePoint lists can be accessed directly through a suitable connector.

A local synchronized path is not always equivalent to a cloud connection. A desktop user may see files through synchronization, while the Power BI service cannot access that local path after publication. For operational refresh, use a supported cloud URL or configure an on-premises data gateway where required.

Permissions are significant: a user who can open a report may not automatically have permission to the underlying SharePoint source, and service credentials must be configured separately.

2.8 Cloud Services and Dataflows

Cloud connectors provide access to services such as Azure storage, Dataverse, Salesforce, Google Analytics, and other software-as-a-service platforms. They often support organizational authentication and can reduce the need for local infrastructure.

A dataflow is a cloud-managed collection of Power Query transformations whose output can be reused by multiple semantic models or applications. Dataflows support centralization of common cleansing logic and can reduce repeated extraction from operational systems. They do not eliminate the need for model design, relationship management, security, refresh planning, or source governance.

3. Power Query as the Preparation Layer

Power Query is an extract, transform, and load (ETL) and extract, load, transform (ELT) tool depending on where transformations execute. Its interface presents applied steps, while the underlying language is Power Query M.

Typical transformations include:

• changing data types;
• removing columns and rows;
• splitting or merging columns;
• replacing values;
• grouping and aggregating;
• pivoting and unpivoting;
• merging queries through joins;
• appending tables vertically;
• invoking custom functions;
• extracting data from nested JSON or XML.

A merge combines columns from related tables using matching keys; an append places rows from one table below rows from another. Confusing these operations is a common beginner error.

Transformation order matters. Removing unnecessary columns and filtering rows early can reduce data volume. Renaming a column before a later step that refers to its old name can cause an error. Steps should be named clearly when the query is important, and source-specific assumptions should be documented.

Power Query also has privacy-level settings that help control how sources are combined. These settings are not a substitute for information-security policy, but they support boundary management when queries combine sources with different privacy characteristics.

4. Connectivity Modes

4.1 Import

In Import mode, Power BI loads a copy of selected data into its in-memory analytical model. Reports generally respond quickly because queries are executed against the model rather than the operational source. Data changes become visible after refresh.

Advantages include:

• strong interactive performance for appropriately sized models;
• broad modeling and DAX capabilities;
• reduced dependence on source availability during report use;
• compression that can make repeated analysis efficient.

Limitations include:

• data is not automatically current between refreshes;
• refresh consumes time and resources;
• large models require capacity, memory, and refresh planning;
• private sources may require a gateway.

Import is often the best default when data volume is manageable and scheduled or triggered refresh provides sufficient timeliness.

4.2 DirectQuery

In DirectQuery mode, Power BI stores metadata and sends queries to the source when users interact with a report. The source therefore remains the primary location for detailed data.

DirectQuery can be useful when:

• the data is too large or changes too frequently for practical import;
• organizational policy requires data to remain in the source;
• near-real-time access is important;
• the source has adequate performance and concurrency capacity.

It introduces trade-offs. Every visual may generate source queries, so report design and source indexing matter. Network latency, database workload, source permissions, query complexity, and concurrency influence user experience. Some Power Query transformations and modeling features are restricted or behave differently compared with Import mode. DirectQuery should be selected because its business requirement justifies the operational cost, not simply because it sounds more current.

4.3 Live Connection

A live connection uses an existing semantic model or Analysis Services model rather than importing source tables into a new local model. The central model contains relationships, measures, and governance rules. Report authors focus on visual design and analysis.

Live connection supports centralized definitions and reduces duplication. Its limitation is reduced local modeling flexibility, although modern Power BI capabilities may permit extending certain connected models in supported scenarios. The exact capabilities depend on the connected model type, tenant configuration, and product version.

A live connection is conceptually different from DirectQuery. DirectQuery generally queries a source system directly through a model configured for that mode; a live connection consumes an existing semantic model or Analysis Services model.

4.4 Composite Models

A composite model combines storage modes or sources within one semantic model. For example, imported historical data may be combined with DirectQuery current data, or a local model may extend an existing semantic model where supported.

Composite designs can balance speed and freshness, but they require careful relationship design. Cross-source relationships, limited relationships, aggregation behavior, security propagation, and query performance can be complex. A model should be tested with realistic filters and concurrent users rather than judged only in Desktop.

4.5 Comparison

5. Selecting a Connectivity Strategy

Selection should begin with requirements rather than technology preference. Consider the following questions:

1. How current must the data be: hourly, daily, minute-by-minute, or event-level?
2. Can the source support expected query volume without harming operational workloads?
3. Is the data volume suitable for an imported model after removing unnecessary detail?
4. Are there regulatory or contractual restrictions on copying data?
5. Who owns definitions, security, and refresh operations?
6. Is a gateway required, and who will maintain it?
7. Will multiple reports require the same cleansed data and measures?
8. What happens if the source is unavailable?

A practical decision rule is:

• choose Import for governed, refreshed analytical data when freshness requirements are moderate;
• choose DirectQuery when source-resident, frequently changing data is genuinely required and the source is designed for the workload;
• choose live connection when a trusted enterprise semantic model already exists;
• choose a composite model when a deliberate combination of historical, current, or centrally governed components provides clear value.

6. Refresh, Gateways, and Credentials

Refresh is not merely a button in Power BI Service. It is an operational chain involving source availability, credentials, transformation success, capacity, and dependencies.

Cloud sources may be reachable directly by the Power BI service. Sources located on a private corporate network commonly require an on-premises data gateway. A gateway is installed and maintained separately from the report and provides a controlled route between the service and eligible private sources.

Credentials should follow least privilege: the identity used for refresh should have only the permissions needed to retrieve required data. Personal accounts are risky for production refresh because employment changes or password policies can interrupt operations. Organizational service identities or managed authentication should be considered where supported.

Refresh planning should include:

• expected duration and refresh window;
• source-system workload;
• failure notification;
• dependency order;
• incremental refresh where supported and justified;
• validation of row counts and key measures;
• a documented owner and recovery procedure.

7. Security, Governance, and Ethical Responsibilities

Connectivity exposes organizational information, so technical success must be accompanied by governance. Important controls include:

• classifying sensitive and personal data before connection;
• restricting credentials and avoiding secrets in query text;
• applying row-level security where users should see different records;
• limiting exported data and report sharing;
• documenting source ownership, lineage, and refresh status;
• respecting licenses, terms of service, and applicable privacy law;
• retaining only data necessary for the stated business purpose.

A report may appear harmless while exposing salary, health, customer, or employee information through exports, drill-through pages, tooltips, or underlying-data access. Data minimization and access review should therefore include the complete user experience, not only the visible chart.

Data quality is also an ethical issue. A connector does not guarantee that data is accurate, complete, or representative. Analysts should identify missing records, duplicate keys, inconsistent definitions, and stale extracts, and should communicate material limitations to decision-makers.

8. Applied Case: Regional Retail Sales Reporting

The following is an illustrative case.

Background

A retail company receives daily sales files from 40 stores, maintains product and customer data in Azure SQL Database, stores operational documents in SharePoint, and has an existing enterprise semantic model containing approved financial measures.

Problem

Regional managers want a report that shows current-day sales, three years of historical trends, product performance, and centrally approved margin calculations. The first prototype imported all data into one model. Refresh required several hours, the database experienced heavy load, and different reports produced different margin figures.

Analysis

The historical sales data changes rarely and is suitable for Import mode. Current-day transactions change frequently and may require more current access. Product data should be obtained from the governed database rather than manually maintained spreadsheets. The approved financial semantic model should remain the authority for margin definitions.

The store files also vary slightly in column order. Combining them without a controlled transformation would create inconsistent results. SharePoint is more appropriate than a local synchronized folder because the files are managed centrally and must be refreshed by the service.

Approach and Solution

The team implemented the following design:

• a SharePoint Folder query filtered valid daily files and applied a standard transformation function;
• historical sales were imported after unnecessary columns were removed and data types were validated;
• current-day sales used a carefully tested DirectQuery component against a source view designed for reporting;
• the central semantic model supplied approved margin measures through a supported connected-model approach;
• product and store dimensions were sourced from Azure SQL through governed views;
• a gateway and organizational credentials were configured where private network access required them;
• refresh monitoring checked file counts, row counts, missing dates, and failed steps.

Outcome

Historical visuals became faster because they used the imported model. Current-day pages remained dependent on database performance but met the managers' freshness requirement. Margin definitions were consistent across reports, and file ingestion no longer depended on an analyst's laptop.

Learning Points

The case illustrates that connectivity mode is an architectural decision. Import, DirectQuery, and governed semantic-model connectivity can coexist, but only when each component has a clear purpose. It also demonstrates that source standardization, credentials, monitoring, and ownership are as important as connector selection.

9. Challenges and Best Practices

Common challenges include schema drift, inaccessible private sources, expired credentials, API throttling, unstable webpages, duplicate keys, timezone confusion, and unexpected changes to business definitions. These problems are reduced by the following practices:

1. Prefer stable, documented sources such as database views, official files, and APIs.
2. Use explicit data types and preserve identifiers as text when appropriate.
3. Filter and select columns early, especially for large sources.
4. Check query folding when source-side execution is expected.
5. Separate staging, transformation, and reporting logic where complexity justifies it.
6. Use parameters for environments, dates, and controlled paths.
7. Name queries and applied steps clearly.
8. Test refresh with a service identity, not only with the developer's credentials.
9. Design for source failure and document ownership.
10. Validate totals against a trusted control report before publication.
11. Treat APIs and webpages as contracts that may change.
12. Test performance with realistic data volume, filters, and concurrent use.

10. Contemporary Developments

Modern Power BI environments increasingly emphasize centralized semantic models, reusable dataflows, cloud lakehouse and warehouse platforms, and near-real-time analytical patterns. These developments can simplify access and improve reuse, but they do not remove fundamental responsibilities. Organizations still need clear data ownership, reliable metadata, security design, cost control, and testing.

AI-assisted features may help users discover fields, generate queries, or summarize reports, but generated results require human validation. An assistant cannot establish that a source is legally usable, that a measure reflects the approved definition, or that a transformation has not excluded important records. AI is therefore an aid to productivity, not a replacement for source governance and analytical judgment.

Chapter Summary

Power BI can connect to files, folders, databases, webpages, APIs, SharePoint, cloud services, and dataflows. The connector determines how Power Query reaches a source, while transformation steps prepare data for analysis. Excel tables and well-structured CSV files are practical file sources; folders support repeatable file ingestion; databases and cloud services provide governed and scalable access; APIs require attention to authentication, pagination, limits, and schema change.

Import stores data in the Power BI model and generally provides strong interactive performance after refresh. DirectQuery leaves detailed data in the source and queries it at report time, offering freshness at the cost of source dependence and performance constraints. A live connection uses an existing semantic model, supporting centralized definitions. Composite models combine modes or sources and can address mixed requirements but require careful testing.

A professional solution evaluates freshness, volume, performance, security, governance, credentials, gateways, refresh, and ownership. The most appropriate architecture is the one that satisfies business requirements reliably while minimizing unnecessary complexity and risk.

Key Terms

Data source: The system, file, service, or platform from which data originates.

Connector: A Power BI mechanism for accessing a particular source type.

Power Query: The data-access and transformation capability used to prepare data.

Import mode: A connectivity mode that loads a copy of data into the Power BI model.

DirectQuery: A mode that sends queries to the source when report users interact.

Live connection: A connection to an existing semantic model or Analysis Services model.

Composite model: A model that combines storage modes or multiple sources.

Semantic model: The tables, relationships, calculations, and metadata used for analysis.

Query folding: Translation of compatible Power Query steps into operations performed by the source.

Gateway: Software that provides controlled connectivity between the Power BI service and private networks.

Refresh: The process of updating imported data or recalculating connected content.

Dataflow: A cloud-managed, reusable collection of Power Query transformations.

API: An application programming interface that provides programmatic access to a service.

Pagination: Retrieving an API result across multiple pages or requests.

Merge: A Power Query operation that joins columns from related tables.

Append: A Power Query operation that combines rows from tables.

Schema drift: Unplanned change in source structure, such as columns being added or renamed.

Row-level security: Rules that restrict records visible to particular users.

Credential: Authentication information used to access a source.

Data lineage: Documentation of how data moves from origin through transformations to reports.

Review Questions

Short Answer Questions

1. What is the difference between a data source, a connector, and a semantic model?
2. Why are Excel tables generally preferable to arbitrary worksheet ranges?
3. Define query folding and explain one benefit of it.
4. What is the principal difference between Import and DirectQuery?
5. Why might a Power BI service refresh require a gateway?

Long Answer Questions

1. Explain the lifecycle of a Power BI data connection from source identification through operational refresh.
2. Compare Import, DirectQuery, live connection, and composite models, including one suitable use case for each.
3. Discuss the main technical risks of using APIs and webpages as data sources.
4. Explain how credentials, privacy, row-level security, and data minimization contribute to responsible BI.
5. Describe best practices for combining recurring files from a SharePoint folder.

Analytical / Application Questions

1. A company needs fast analysis of five years of sales and minute-by-minute monitoring of today's transactions. Recommend a connectivity architecture and justify your choice.
2. A CSV contains customer codes such as 000781, but Power Query converts them to numbers. Explain the problem and propose a corrective transformation and validation approach.
3. A DirectQuery report is slow and causes heavy database load. Identify at least four factors to investigate and recommend two possible redesigns.

Multiple Choice Questions with Answers

Question 1

Which connectivity mode loads a copy of data into the Power BI model?

A. Import

B. DirectQuery

C. Live connection

D. API pagination

Correct Answer: A. Import

Question 2

Which Power Query operation combines rows from one table below rows from another table?

A. Merge

B. Append

C. Pivot

D. Group By

Correct Answer: B. Append

Question 3

What is the main purpose of query folding?

A. To encrypt report pages

B. To translate compatible transformations so the source performs them

C. To create a gateway automatically

D. To convert every source into a CSV file

Correct Answer: B. To translate compatible transformations so the source performs them

Question 4

Which source is usually most suitable for combining recurring files with a consistent structure?

A. Folder connector

B. Live connection

C. Dashboard theme

D. Row-level security rule

Correct Answer: A. Folder connector

Question 5

Which mode generally queries the source when users interact with a report?

A. Import

B. DirectQuery

C. Static export

D. Dataflow storage only

Correct Answer: B. DirectQuery

Question 6

What does a live connection primarily consume?

A. An existing semantic model or Analysis Services model

B. Only a local text file

C. A webpage screenshot

D. A Power Query error log

Correct Answer: A. An existing semantic model or Analysis Services model

Question 7

Why can an API connection return incomplete data if pagination is ignored?

A. The API may return only one page of records

B. Power BI deletes duplicate columns automatically

C. CSV delimiters become invalid

D. A gateway blocks all cloud services

Correct Answer: A. The API may return only one page of records

Question 8

Which practice best protects a production refresh process?

A. Use a departing employee's personal password

B. Embed an API secret in a public report

C. Use controlled organizational credentials with least privilege

D. Disable all source permissions

Correct Answer: C. Use controlled organizational credentials with least privilege

Question 9

A company wants imported historical data and current data queried from a source in one model. Which approach is most relevant?

A. Composite model

B. CSV encoding

C. Worksheet formatting

D. Append-only text export

Correct Answer: A. Composite model

Question 10

What is a likely reason for using an on-premises data gateway?

A. To connect the Power BI service to a private network source

B. To create Excel tables

C. To replace all semantic models

D. To format webpage headings

Correct Answer: A. To connect the Power BI service to a private network source