Connecting to Data Sources — Connect to common sources (Excel/CSV, SQL databases, web) (intro) - Microsoft Power Bi

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).

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)

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

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

1
Step 1 — Open Get Data and Select SQL Server

In 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.
2
Step 2 — Load the Excel Product Catalog

Click 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).
3
Step 3 — Connect to the Exchange Rate API

Click 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.
4
Step 4 — Define Relationships in the Model

Switch 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	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.

Connector strengths and limitations summary

✦

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 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

Introductory vs. advanced connectivity concepts

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

Question 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?

Show answer

Schema inference relies on sampling a subset of rows (typically the first 200) to guess column types, which means that if a column contains mostly integers but has a text value on row 500, the engine may assign an integer type and then fail on the anomalous row during load. An explicit schema, defined by the database's DDL (CREATE TABLE statements), guarantees type consistency because the RDBMS enforces constraints at write time. The practical consequence is that CSV-based pipelines require defensive type-checking steps in Power Query (e.g., Table.TransformColumnTypes() with error handling), whereas SQL sources rarely produce type-mismatch errors. This distinction highlights the general principle that schema-on-read (CSV) trades upfront rigor for flexibility, while schema-on-write (SQL) enforces data quality at ingestion.

Question 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?

Show answer

The raw data volume is 500,000 rows × 12 columns × 25 bytes = 150,000,000 bytes = 150 MB (dividing by 10⁶) or approximately 143 MiB (dividing by 2²⁰). With a 10× compression ratio, the in-memory VertiPaq representation would be approximately 150 MB ÷ 10 = 15 MB. In practice, VertiPaq's columnar compression can achieve even better ratios on low-cardinality text columns through dictionary encoding and run-length encoding. This calculation underscores why Import mode is practical for datasets well below the 1 GB per-dataset limit in shared capacity — the compression engine makes it feasible to hold surprisingly large tables in RAM.

Question 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?

Show answer

Steps (1) and (2) are excellent candidates for query folding. The filter translates to a SQL WHERE clause (WHERE Status = 'Active') and the column removal translates to excluding InternalNotes from the SELECT list. Step (3) — adding a custom column using M string concatenation — will likely break query folding because the M expression & " " may not map directly to a SQL CONCAT or + operator depending on the connector version, though some recent connector updates do fold simple concatenations. To verify, right-click the step in the Applied Steps pane and look for the 'View Native Query' option. If it is available and clickable, the step (and all preceding steps) have been successfully folded. If it is grayed out, folding broke at that step, and all subsequent processing occurs in the local Power Query engine. A performance-conscious analyst would reorder or restructure steps to maximize the foldable portion of the query.

Question 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.

Show answer

First, use Get Data → Web and enter the API URL. Select Anonymous authentication if the endpoint is public. In the Power Query Editor, the initial result is a JSON record. Apply Json.Document(Web.Contents(url)) to parse the response, then navigate to the 'rates' field using Record.Field(Source, "rates"). Convert the record to a table with Record.ToTable(), which yields two columns: Name (currency code) and Value (exchange rate). Rename these to CurrencyCode and RateToUSD, and change Value's type to Decimal. To handle the 100-request daily limit, configure a scheduled refresh in the Power BI Service to run once per day (e.g., at 6:00 AM UTC), ensuring you consume only 1 of the 100 allowed requests. If you need historical rates, consider building a dataflow that appends each day's snapshot to a cumulative table, preventing repeated calls for historical data. Additionally, wrap the Web.Contents() call with error handling (try ... otherwise) to gracefully degrade if the API returns a 429 (Too Many Requests) status code.

Question 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?

Show answer

Both positions have merit, and the optimal solution depends on organizational maturity and data criticality. The data engineering team is correct that a centralized warehouse provides schema enforcement (schema-on-write), query folding for performance, and governance through RBAC and audit trails — essential for regulated environments and mission-critical KPIs. The analyst is correct that requiring every dataset to pass through a warehouse introduces latency in the analytics lifecycle; exploratory analyses and ad-hoc datasets (e.g., a one-time vendor spreadsheet) should not require a JIRA ticket and a staging pipeline. A hybrid approach uses the SQL warehouse for core fact and dimension tables (via DirectQuery or Import with incremental refresh), while permitting analysts to load supplementary CSV/Excel files as 'self-service' dimension tables. Power BI's privacy levels become critical here: the warehouse should be set to 'Organizational' and the CSV files to 'Private' or 'Organizational' depending on sensitivity, preventing accidental data leakage between security domains. Composite models allow the same Power BI report to contain DirectQuery tables (warehouse) alongside Import tables (CSV), enabling this coexistence. The key governance guardrail is establishing clear ownership: the data engineering team certifies the warehouse tables as authoritative, while self-service CSV tables carry a 'user-managed' label and are excluded from executive-facing reports.

SUMMARY

Lesson Summary

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.