Connecting to Data Sources

Analogy categoryAnalogies

Get Data: Connecting to Your First Source

The Get Data dialog in Power BI Desktop lists connectors grouped by category: File (Excel, CSV, PDF, JSON), Database (SQL Server, PostgreSQL, MySQL, Oracle), Power Platform (Dataverse, Power BI datasets), Azure (Synapse, Data Lake), and Online Services (SharePoint, Salesforce, Google Analytics), among hundreds more. After selecting a connector and supplying connection details, the Navigator window shows a preview of the available tables or sheets so you can select exactly which ones to load before committing.

🏏

Cricket analogy: The Get Data dialog is like a broadcaster choosing which camera feed to pull from — stump cam, drone cam, or the official scorer's feed — before previewing it live.

Authentication Methods

Power BI supports several authentication methods depending on the connector: Windows (uses your current domain credentials), Basic (a manually entered username and password), Database (credentials specific to that database engine), Organizational account / OAuth2 (Microsoft or Azure Active Directory sign-in used for services like SharePoint or Dataverse), and Anonymous or API key for some web and REST sources. Credentials are stored per data source (not per report) under File > Options and settings > Data source settings, so the same source shares one set of credentials across every report that uses it.

🏏

Cricket analogy: Choosing Windows vs Basic vs OAuth authentication is like a stadium choosing between a season-ticket turnstile scan, a printed paper ticket check, or a mobile app QR code to let fans in.

Connectivity Modes: Import vs. DirectQuery vs. Live Connection

Import mode copies data into Power BI's in-memory VertiPaq engine at load time; it's fast to query and supports the full DAX language, but the model only reflects data as of the last refresh. DirectQuery leaves the data at the source and translates every visual interaction into a live query against it, so results are always current but performance depends entirely on the source system and some DAX functions aren't supported. Live Connection is a special case used to connect directly to an existing Analysis Services model or a published Power BI dataset, letting multiple reports share one central model without duplicating data.

🏏

Cricket analogy: Import is like downloading the full scorecard to study offline on a flight, DirectQuery is like watching a live ball-by-ball feed that depends on your signal, and Live Connection is like tuning into the official broadcaster's single shared feed everyone watches simultaneously.

Choosing the Right Mode

The practical choice usually comes down to data freshness versus performance and DAX flexibility. A sales dashboard reviewed weekly is a good fit for Import, since a scheduled refresh keeps it reasonably current while giving fast, feature-complete DAX. An operational dashboard monitoring live warehouse stock levels is a better fit for DirectQuery, where staleness is unacceptable even at some performance cost. Composite models, introduced to blend Import and DirectQuery tables in a single model, let you Import slow-changing dimension tables while keeping a large, fast-changing fact table on DirectQuery.

🏏

Cricket analogy: Choosing Import for a season-long stats dashboard versus DirectQuery for live ball-by-ball win probability is like a broadcaster deciding whether to prep pre-recorded highlight packages versus running a live analytics overlay during the match.

powerquery-m

let
    Source = Sql.Database("sql-prod-01.contoso.com", "SalesDW"),
    dbo_FactSales = Source{[Schema="dbo",Item="FactSales"]}[Data],
    FilteredRows = Table.SelectRows(dbo_FactSales, each [OrderDate] >= #date(2025, 1, 1))
in
    FilteredRows

The Power Query Editor (opened via Transform Data) previews and reshapes a source before anything loads into the model, so you can filter rows, remove columns, or fix data types without touching the original file or database.

DirectQuery does not support the full DAX function library and can be noticeably slower for complex visuals, since every interaction sends a new query to the source system in real time. Test performance against realistic data volumes before committing to DirectQuery for a production report.

Get Data groups hundreds of connectors by category: File, Database, Power Platform, Azure, and Online Services.
The Navigator window previews tables before you commit to loading them.
Authentication methods include Windows, Basic, Database, Organizational/OAuth2, and Anonymous/API key.
Credentials are stored per data source under Data source settings, shared across reports using that source.
Import mode caches data in-memory for speed and full DAX support but requires scheduled refresh.
DirectQuery queries the source live for real-time freshness at the cost of performance and DAX limitations.
Live Connection shares a single central model (Analysis Services or a published dataset) across multiple reports.