Connecting to Data Sources — Connect to common sources...

SECTION 1

Historical Context & Motivation

Before the advent of modern business intelligence platforms, organizations relied on manual, ad-hoc processes to extract meaning from data. Analysts routinely exported tables from databases into spreadsheet applications, hand-crafted pivot tables, and pasted charts into presentation decks — a workflow that was brittle, error-prone, and fundamentally non-scalable. The core challenge was not a shortage of data but rather the lack of a unified abstraction layer that could connect heterogeneous sources into a single analytical model. Microsoft recognized this gap and, building on years of experience with SQL Server Reporting Services and Excel Power Query, released Power BI Desktop in 2015 as a free-standing tool designed to democratize data connectivity and visualization.

1985

Excel 1.0 Released

Microsoft ships Excel for the Macintosh, establishing the flat-file spreadsheet as the dominant analytical artifact in business and academia. CSV interchange becomes the lingua franca of tabular data.

1995

ODBC & OLEDB Mature

Standardized driver interfaces such as ODBC and OLE DB allow desktop applications to query relational databases like SQL Server, Oracle, and MySQL without vendor-specific code, laying the groundwork for universal data connectivity.

2010

Power Query Origins

Microsoft introduces Power Pivot and later Power Query as Excel add-ins, bringing ETL (Extract, Transform, Load) capabilities to end-user spreadsheets and previewing the connector architecture that Power BI would inherit.

2015

Power BI Desktop Launched

Power BI Desktop ships with over 60 built-in connectors covering files, databases, online services, and web APIs. The Get Data dialog becomes the central entry point for all data acquisition workflows.

2023

300+ Connectors

The Power BI connector ecosystem exceeds 300 certified sources, including cloud-native platforms like Snowflake, Databricks, and Google BigQuery, along with REST/GraphQL web endpoints — reflecting the polyglot reality of modern data engineering.

The fundamental question that Power BI's connector framework addresses is deceptively simple: How can an analyst bring data from disparate, structurally different sources — a CSV on a file share, a production SQL Server, a public JSON API — into a single semantic model without writing bespoke integration code? Understanding the answer requires familiarity with each connector type's protocol, authentication model, and data-shape implications, which is precisely what this lesson covers.

SECTION 2

Core Principles of Data Connectivity

Power BI's data acquisition architecture rests on a small set of principles that, once internalized, make every connector feel familiar regardless of the underlying source technology. These principles mirror patterns you have likely encountered in systems programming courses: abstraction of I/O, protocol negotiation, schema inference, and lazy versus eager evaluation.

Connector Abstraction

Every data source is accessed through a connector — a plug-in module that encapsulates the protocol (file I/O, ODBC/TDS, HTTP) and exposes a uniform tabular interface to the Power Query engine. This abstraction means the transformation layer operates identically whether the upstream source is a CSV or an Oracle database.

Query Folding

When connecting to databases, Power Query attempts query folding — translating M-language transformation steps into native SQL and pushing computation to the server. This concept is analogous to predicate pushdown in distributed query planners, reducing data transfer and latency.

Schema Inference

For schema-less sources like CSV files and JSON endpoints, the engine samples rows to infer column types (text, integer, decimal, datetime). Understanding that schema inference is probabilistic rather than deterministic is key to avoiding type-mismatch errors downstream.

Import vs. DirectQuery

Power BI offers two storage modes: Import (data is cached in an in-memory columnar store) and DirectQuery (queries are sent to the source at report render time). The tradeoff echoes the classic materialized-view versus live-query debate in database theory.

Authentication & Privacy Levels

Each connector negotiates credentials — Windows, database, OAuth2, API key — and applies a privacy level (Public, Organizational, Private) that governs whether data from different sources can be combined, preventing accidental information leakage between security domains.

✦ KEY TAKEAWAY

Think of the Power BI connector layer as an adapter pattern from object-oriented design. Just as an adapter wraps a class with an incompatible interface so that it conforms to a target interface, each Power BI connector wraps a source-specific protocol (ODBC, REST, file I/O) and exposes it as a uniform stream of tables to the Power Query engine. Regardless of whether you load a local CSV or a remote PostgreSQL view, the downstream M-language code looks identical.

SECTION 3

Visual Explanation — The Data Connectivity Pipeline

The diagram shows three common data source types on the left (Excel/CSV via file I/O, SQL databases via TDS/ODBC, and web APIs via HTTPS). Each feeds into the Power Query engine, which performs schema inference, type detection, query folding, and transformations. In Import mode the cleaned data is compressed into the VertiPaq columnar store; in DirectQuery mode (dashed line, SQL only), queries bypass the local cache and execute on the source at report render time.

Notice how the Power Query engine serves as the single gateway regardless of source. This design is intentional: it decouples data acquisition from data modeling and visualization, much as the Model-View-Controller pattern separates concerns in application architecture. When you change an upstream source — say, migrating from a CSV file to a SQL view — you modify only the connector configuration; the downstream DAX measures and report visuals remain untouched.

SECTION 4

How Each Connector Type Works

Excel and CSV Connectors

The Excel connector reads .xlsx and .xls files by parsing the Open XML (or legacy BIFF) binary format and enumerating each worksheet and named range as a navigable table. The CSV/Text connector reads delimited flat files, auto-detecting the delimiter (comma, tab, semicolon, pipe) and the file encoding (UTF-8, UTF-16, ANSI). Because these files have no embedded schema, the Power Query engine samples the first 200 rows to perform type inference — a heuristic that can misclassify columns when outlier values appear later in the file. In M code, you invoke Excel.Workbook() or Csv.Document() respectively, and both return a table of tables from which you select the desired sheet or partition.

💡 Tip: Promote Headers

When loading CSV files, the first row often contains column headers rather than data. Use the Table.PromoteHeaders() M function (or the 'Use First Row as Headers' button in the Power Query Editor) to prevent header values from being treated as data rows. Failing to do so is one of the most common beginner mistakes.

SQL Database Connector

The SQL Server connector uses the Tabular Data Stream (TDS) protocol — the same wire protocol that SQL Server Management Studio employs. You supply a server hostname, an optional database name, and an optional native SQL statement. When you navigate tables through the GUI, Power BI issues SELECT * FROM INFORMATION_SCHEMA.TABLES behind the scenes to enumerate available objects. The critical performance feature here is query folding: if you apply filter, sort, or group-by steps in the Power Query Editor, the engine translates those M-language operations into a SQL WHERE, ORDER BY, or GROUP BY clause and sends the optimized query to the server, thereby minimizing network transfer. You can verify whether folding occurred by right-clicking any step in the Applied Steps pane and selecting 'View Native Query'; if the option is grayed out, folding has broken at that step.

Web Connector

The Web connector issues an HTTP GET request to a user-supplied URL and parses the response body. If the content type is text/html, the engine extracts all <table> elements from the DOM and presents each as a selectable table — a convenient mechanism for scraping publicly available tabular data. If the content type is application/json, the engine deserializes the JSON into a hierarchical record/list structure that you can expand and flatten using Json.Document() and Table.FromRecords(). Authentication options include Anonymous, Basic, API Key (passed as a query parameter or header), and OAuth2. Because web sources are inherently volatile, Power BI's privacy firewall is especially strict: combining a Web source marked as 'Public' with an organizational SQL database will trigger a privacy-level conflict unless you explicitly configure compatible levels.

📝 Note: OData & REST APIs

For RESTful APIs that follow the OData protocol, Power BI provides a dedicated OData Feed connector that supports server-side filtering ($filter), pagination ($skip, $top), and query folding — significantly more efficient than the generic Web connector for large datasets.

SECTION 5

Detailed Connector Comparison

Choosing the right connector and storage mode is an architectural decision with implications for refresh latency, report interactivity, and data governance. The following visual and table provide a side-by-side comparison of the three introductory source types across key dimensions.

Side-by-side comparison of the three introductory connector types. Key differences include query folding support (only SQL connectors fully support it), schema definition (explicit DDL for SQL vs. inferred for files and web), and available storage modes (only SQL connectors support DirectQuery).

Practical comparison of Excel/CSV, SQL, and Web connectors

Dimension	Excel / CSV	SQL Database	Web / API
Typical Volume	< 1 M rows (Excel limit: ~1.05 M rows per sheet)	Millions to billions of rows; constrained by server resources	Varies; API rate limits and pagination apply
Refresh Method	Scheduled refresh reads the file again from disk or SharePoint	Scheduled refresh re-executes query; DirectQuery is live	Scheduled refresh re-issues HTTP GET; subject to endpoint availability
Data Governance	Low — files are easily copied, versioning is manual	High — RBAC, audit logs, centralized schema management	Medium — depends on API provider's access controls
Best Use Case	Ad-hoc analyses, prototyping, small static datasets	Enterprise reporting, real-time dashboards, regulated environments	External data enrichment (weather, finance, social media)

SECTION 6

Worked Example — Connecting to Three Sources

Suppose you are building a sales performance dashboard. Your fact table lives in an Azure SQL Database, your product catalog is maintained as an Excel file on SharePoint, and you need to enrich orders with real-time exchange rates from a public JSON API. The following walkthrough demonstrates how to connect to all three within a single Power BI Desktop file.

Multi-Source Dashboard Connection

Step 1 — Open Get Data and Select SQL ServerIn Power BI Desktop, click Home → Get Data → SQL Server. In the dialog, enter the server name (e.g., myserver.database.windows.net) and the database name (e.g., SalesDB). Expand 'Advanced options' and paste a native query: SELECT OrderID, ProductID, Quantity, UnitPrice, OrderDate FROM dbo.Orders WHERE OrderDate >= '2024-01-01'. Select Import mode and click OK.

Power BI establishes a TDS connection, authenticates via Azure Active Directory, and previews the result set. Because we supplied a native SQL statement, the query folds completely.

Step 2 — Load the Excel Product CatalogClick Get Data → Excel Workbook and browse to the SharePoint-synced file ProductCatalog.xlsx. In the Navigator pane, select the Products sheet. Before clicking Load, click 'Transform Data' to open the Power Query Editor. Promote the first row to headers using Table.PromoteHeaders() and change the ListPrice column type from 'Any' to 'Decimal Number'.

The Products table loads with correct types: ProductID (Int64), ProductName (Text), Category (Text), ListPrice (Decimal).

Step 3 — Connect to the Exchange Rate APIClick Get Data → Web and enter the URL https://api.exchangerate.host/latest?base=USD. When prompted for authentication, select Anonymous (this is a public API). Power BI returns a JSON record. In the Power Query Editor, expand the rates record into columns using Record.ToTable() and rename the resulting columns to CurrencyCode and RateToUSD.

The ExchangeRates table contains ~170 rows — one per supported currency — with CurrencyCode (Text) and RateToUSD (Decimal) columns.

Step 4 — Define Relationships in the ModelSwitch to the Model view. Create a one-to-many relationship from Products[ProductID] to Orders[ProductID]. If orders include a CurrencyCode column, relate it to ExchangeRates[CurrencyCode]. These relationships enable cross-source DAX measures such as Revenue_USD = SUMX(Orders, Orders[Quantity] * Orders[UnitPrice] * RELATED(ExchangeRates[RateToUSD])).

A star-schema model with Orders as the fact table and Products / ExchangeRates as dimension tables is now ready for visualization.

SECTION 7

Strengths, Limitations, and Tradeoffs

Connector strengths and limitations summary

Connector	Strengths	Limitations
Excel / CSV	Ubiquitous format; zero infrastructure required; excellent for rapid prototyping; supports folder-based loading for batch ingestion of multiple files.	No query folding; schema inference errors; row limit (~1.05 M per sheet in Excel); poor version control; manual refresh required if files change.
SQL Database	Full query folding; explicit schema; supports DirectQuery for real-time data; strong governance (RBAC, audit); handles massive datasets.	Requires server infrastructure and DBA support; firewall/VPN configuration for on-premises sources; DirectQuery introduces per-query latency.
Web / API	Access to vast external datasets (financial markets, weather, government data); OAuth2 support for authenticated services; flexible JSON/HTML parsing.	No query folding (except OData); rate limiting and pagination add complexity; endpoint availability is outside your control; privacy-level conflicts are common.

✦ KEY TAKEAWAY

In software architecture we often speak of the CAP theorem — the impossibility of simultaneously satisfying consistency, availability, and partition tolerance. Connector selection in Power BI involves an analogous tradeoff triangle among ease of access (Excel/CSV wins), performance at scale (SQL databases win), and breadth of external data (Web/API wins). The best production solutions typically combine all three, leveraging each connector's strengths while mitigating its weaknesses through thoughtful data modeling.

SECTION 8

Connection to Advanced Data Engineering

The three connector types introduced in this lesson are the foundational building blocks, but modern data platforms extend these concepts significantly. As your Power BI practice matures, you will encounter scenarios that demand advanced connectivity patterns — composite models that mix Import and DirectQuery tables, dataflows that centralize ETL in the Power BI Service, and custom connectors built with the Power Query SDK using the M language. Understanding where the introductory connectors end and these advanced patterns begin is essential for making sound architectural decisions.

Introductory vs. advanced connectivity concepts

Introductory Concept	Advanced Extension
CSV file loaded from local disk	Azure Data Lake Storage Gen2 connector with hierarchical namespace, Delta Lake format, and incremental refresh policies
Single SQL Server connection in Import mode	Composite model combining DirectQuery over Azure Synapse Analytics with imported reference tables; row-level security defined via DAX
Web connector with anonymous JSON endpoint	Custom M connector using the Power Query SDK, implementing OAuth2 PKCE flow, pagination handler, and schema contracts for a proprietary REST API
Manual scheduled refresh	Dataflows (Power Query Online) with computed entities, linked entities across workspaces, and incremental refresh partitions for multi-terabyte fact tables
Privacy levels set per source in Desktop	On-premises data gateway managing credentials centrally, with Azure Private Link ensuring SQL traffic never traverses the public internet

As a computer science student, you should recognize that Power BI's connector architecture is essentially a plugin-based middleware layer — not unlike JDBC in the Java ecosystem or SQLAlchemy's dialect system in Python. Mastering the introductory connectors gives you fluency with the abstraction; the advanced patterns simply extend that abstraction to handle higher throughput, stricter security, and more complex data topologies.

SECTION 9

Practice Problems

PROBLEM 1 — CONCEPTUAL

Explain the difference between schema inference (as used by the CSV connector) and explicit schema (as provided by a SQL database). Why might schema inference lead to downstream errors that an explicit schema would prevent?

PROBLEM 2 — BASIC CALCULATION

A CSV file contains 500,000 rows with 12 columns. Each cell averages 25 bytes. Estimate the uncompressed file size in megabytes. If Power BI's VertiPaq engine achieves a typical 10× compression ratio on import, approximately how much memory will the loaded table consume?

PROBLEM 3 — INTERMEDIATE

You are loading data from a SQL Server using Import mode. In the Power Query Editor, you apply the following steps: (1) filter rows where Status = 'Active', (2) remove the InternalNotes column, (3) add a custom column FullName = [FirstName] & " " & [LastName]. Which of these steps are likely to be query-folded into SQL, and which will execute locally? How would you verify this in Power BI Desktop?

PROBLEM 4 — APPLIED

Your organization's financial reporting dashboard requires daily exchange rates from a public REST API that returns JSON. The API has a rate limit of 100 requests per day, and the JSON response nests rates inside an object like {"base":"USD","rates":{"EUR":0.92,"GBP":0.79,...}}. Describe, step by step, how you would configure this in Power BI, including the M functions needed to flatten the JSON, and explain how you would handle the rate limit with scheduled refreshes in the Power BI Service.

PROBLEM 5 — CRITICAL THINKING

A data engineering team proposes replacing all CSV-based data loads in Power BI with a centralized SQL data warehouse, arguing that it eliminates schema inference issues, enables query folding, and improves governance. A business analyst pushes back, arguing that CSV loads are faster to set up and give analysts autonomy without waiting for the data engineering team's backlog. Evaluate both positions. Under what conditions would you recommend a hybrid approach, and how would Power BI's privacy levels and composite models factor into that design?

SUMMARY

Lesson Summary

Power BI's data connectivity layer is built on a connector abstraction that normalizes heterogeneous sources into a uniform tabular interface consumed by the Power Query (M) engine. The three introductory connector families — Excel/CSV (file I/O, schema-inferred, Import only), SQL databases (TDS/ODBC, explicit schema, full query folding, Import or DirectQuery), and Web/API (HTTP, JSON/HTML parsing, Import only) — cover the vast majority of introductory use cases.

Key architectural decisions include choosing between Import mode (data cached in the VertiPaq columnar store for fast queries) and DirectQuery (live queries to the source for real-time freshness), understanding schema inference limitations for flat files, maximizing query folding for SQL sources, and configuring privacy levels to prevent cross-domain data leakage. Mastering these foundational connectors prepares you for advanced patterns including composite models, dataflows, custom connectors, and enterprise gateway configurations.