Data Source Types and Connections

Quick Answer: Power BI connects to 100+ data sources through built-in connectors. The most common are Excel files, SQL Server databases, Azure services, SharePoint, and Power Platform sources like Dataverse. Each connector determines what storage modes are available and whether query folding is supported.

Connecting to data is the foundation of every Power BI project. The PL-300 expects you to know which connectors to use, how to configure them, and the implications of each connection type.

Connector Categories

Power BI organizes its 100+ connectors into these categories:

Connecting to File Sources

Excel Files

Excel is the most common entry point for Power BI data:

Home tab → Get Data → Excel Workbook → Select file → Choose tables/sheets

Key considerations:

• Power BI imports from named tables or worksheet ranges
• Data should be in a tabular format with headers in row 1
• Merged cells, subtotals, and empty rows cause import issues
• Excel files stored on SharePoint or OneDrive can be set up for automatic refresh

CSV and Text Files

CSV files are loaded using the Text/CSV connector:

• Power BI auto-detects delimiters (comma, tab, semicolon)
• Encoding is detected automatically but can be overridden
• First row is used as column headers by default
• Large CSV files (100 MB+) may benefit from conversion to Parquet

JSON Files

JSON data is semi-structured and requires transformation:

• Power BI parses JSON into tables using the JSON connector
• Nested objects appear as Record types that must be expanded
• Arrays appear as List types that must be expanded
• The Json.Document() function in M handles parsing

Parquet Files

Parquet is a columnar storage format increasingly common in modern data platforms:

• Highly compressed and optimized for analytical queries
• Native connector in Power BI Desktop
• Key format for Direct Lake mode with Microsoft Fabric
• Supports complex data types including nested structures

Connecting to Databases

SQL Server

SQL Server is the most commonly tested database connector:

Get Data → SQL Server → Enter server name → Optional: database name

Connection options:

• Import mode: Data is copied into the Power BI model (default)
• DirectQuery: Queries are sent to the server at report time
• Advanced options: Custom SQL statement, command timeout, relationship detection

Authentication methods:

• Windows authentication (Kerberos/NTLM)
• Database authentication (SQL login)
• Microsoft account
• Azure Active Directory

Azure SQL Database

Similar to SQL Server but cloud-hosted:

• Requires the full server name: servername.database.windows.net
• Supports Azure AD authentication with MFA
• Firewall rules must allow the connection
• Supports both Import and DirectQuery

Azure Synapse Analytics

For large-scale analytical workloads:

• Dedicated SQL pools and serverless SQL pools
• Optimized for massive parallel processing (MPP)
• DirectQuery is recommended for large datasets
• Supports the same authentication methods as Azure SQL

Connecting to Power Platform Sources

Microsoft Dataverse

Dataverse (formerly Common Data Service) is the data platform underlying Dynamics 365 and Power Platform:

• Contains standard and custom tables (entities)
• Supports complex data types and relationships
• Accessible via the Dataverse connector or OData feed
• Row-level security from Dataverse can be leveraged

Power BI Dataflows

Dataflows provide reusable ETL logic in the Power BI Service:

• Created using Power Query Online
• Output is stored in Azure Data Lake Storage (Gen2)
• Other reports and models can connect to dataflow outputs
• Promotes data preparation reuse across the organization

Shared Semantic Models (Power BI Datasets)

Connect to an already-published Power BI dataset:

• Live connection: No local model; all measures and tables come from the shared model
• DirectQuery to a dataset: Build a composite model on top of a shared dataset
• Promotes a "single source of truth" pattern
• Changes to the shared model automatically propagate

Connecting to Azure Sources

Azure Data Lake Storage Gen2

For organizations using data lakes:

• Connect using the Azure Data Lake Storage Gen2 connector
• Authenticate with Azure AD or account key
• Browse and import CSV, Parquet, or JSON files from the lake
• Foundation for Direct Lake mode with Fabric

Azure Blob Storage

Simple cloud storage for unstructured data:

• Connect via the Azure Blob Storage connector
• Similar to Data Lake but without hierarchical namespace
• Good for ad-hoc file imports

Connecting to Online Services

SharePoint Online

For team-based Excel and file sharing:

• Use the SharePoint Online List connector for lists
• Use the SharePoint Folder connector for files in document libraries
• Requires SharePoint site URL and appropriate permissions
• Supports automatic refresh through the Power BI Service

Web Connector

For public or API-based data:

• Enter a URL to import HTML tables or API responses
• Supports Basic, Web API, and Anonymous authentication
• Advanced mode allows setting HTTP headers and URL parts
• Useful for REST APIs that return JSON or XML

On the Exam

The PL-300 frequently tests:

• Selecting the correct connector for a given data source
• Understanding which connectors support DirectQuery vs. Import only
• Knowing authentication options for different source types
• Recognizing when to use a shared semantic model vs. creating a new one
• Understanding the role of dataflows in enterprise BI