Power BI Data Sources - How to Connect to the Right Data the Right Way

Every Power BI project starts the same way. Someone clicks "Get data" and stares at a wall of connector options. There are hundreds of them now. SQL Server, Oracle, Snowflake, Excel, SharePoint, REST APIs, Parquet files, Cosmos DB - the list scrolls on and on. Microsoft documents the full set in their desktop data sources reference, and honestly, the sheer volume can be overwhelming.

The good news is that Power BI probably connects to whatever you're using. We've worked with Australian organisations running everything from legacy Oracle databases to brand new Fabric lakehouses, and we've yet to hit a scenario where Power BI simply couldn't connect. The bad news is that not all connectors are equal. Some are rock solid and battle tested. Others are in beta, barely documented, and will make your life difficult in production.

Here's what we've learned about the connector categories and how to think about which ones to use.

The Get Data Experience

When you hit "Get data" in Power BI Desktop, you see a dialog box that organises connectors into categories - File, Database, Microsoft Fabric, Power Platform, Azure, Online Services, and Other. There's also an "All" view if you want to browse the full list.

The categorisation is mostly logical, though there are quirks. The Web connector lives under "Other" even though it's one of the most commonly used. SharePoint Online List sits under "Online Services" but SharePoint List (on-premise) is under "Other." You get used to it.

The more useful feature is the search box at the top. If you know what you're connecting to, just type the name. Saves you scrolling through seven categories trying to remember where Microsoft decided to put it.

File Connectors - Where Most Projects Start

The File category covers Excel, CSV, Text, JSON, XML, Parquet, PDF, and folder sources. For a lot of Australian businesses, especially smaller ones, this is where the Power BI journey begins. The finance team has an Excel workbook. Someone in operations maintains a CSV export. A vendor drops a JSON file into a shared folder every night.

Excel is the one we see most. It works fine for getting started. The connector reads named tables, named ranges, and sheets. Our advice is always the same - use named tables in your Excel files, not just raw sheets. Named tables give you a stable schema that doesn't break when someone inserts a row above your data.

CSV and Text are straightforward. Power BI auto-detects delimiters, encoding, and data types reasonably well. Where it gets tricky is when you're connecting to a folder of CSV files that arrive daily. The Folder connector handles this - it combines all files matching a pattern into a single table. Genuinely useful for automated report pipelines.

Parquet is the one to watch. If you're working with data engineers who produce Parquet files (common in modern data platforms and Fabric lakehouses), the Parquet connector is efficient and handles large files well. It's column-oriented, compressed, and typed - everything CSV isn't. We're seeing more Australian organisations standardise on Parquet as their interchange format.

PDF is a connector that exists. I'll leave it at that. If you're extracting tables from PDFs as a regular data source, there's probably a better way to get that data. PDF extraction is inherently unreliable because the format was designed for printing, not structured data. Use it for one-off analysis, not for production reports.

Database Connectors - The Workhorses

This is where the serious work happens. The Database category includes SQL Server, Oracle, PostgreSQL, MySQL, Snowflake, Google BigQuery, Amazon Redshift, SAP HANA, Teradata, and dozens more.

SQL Server is the gold standard. The connector is mature, fast, supports both Import and DirectQuery, handles parameterised queries, and works reliably through the on-premise data gateway. If your data is in SQL Server, you're in the best possible position. Azure SQL gets the same connector and works without a gateway since it's cloud-to-cloud.

Oracle is solid but requires a separate Oracle client installation on the machine running Power BI Desktop and on your gateway server. This trips people up constantly. The connector is fine once the client is installed, but that extra dependency adds friction. Make sure your IT team knows about it upfront.

PostgreSQL and MySQL both work well. We've seen PostgreSQL usage grow significantly among Australian businesses over the last few years, particularly those running open-source stacks. The connector is reliable for Import mode. DirectQuery support exists but your mileage may vary depending on query complexity.

Snowflake is popular with organisations that have invested in a cloud data warehouse. The connector supports both Import and DirectQuery, and Snowflake's architecture handles the query concurrency that DirectQuery demands better than most traditional databases. If you're running Snowflake, connecting Power BI to it is genuinely pleasant.

BigQuery has improved a lot. Earlier versions were slow and finicky. The current version handles large datasets better and the Google Entra ID variant simplifies authentication for organisations already using Microsoft identity. If you're deeply invested in Google Cloud, Looker might be a more natural fit than Power BI. I say this as someone who sells Power BI consulting - use the right tool for your stack.

A word on beta connectors. You'll see quite a few database connectors marked "Beta" or "Preview" in the Get Data dialog. Microsoft explicitly says these shouldn't be used in production. In practice, some have been in beta for years and work fine. Others have real issues - incomplete type mappings, missing features, unexpected behaviour under load. If you need to use one, test thoroughly and keep ODBC as a fallback.

Microsoft Fabric and Power Platform

These two categories are growing fast as Microsoft pushes its Fabric ecosystem.

Microsoft Fabric connectors include semantic models, dataflows, warehouses, lakehouses, and KQL databases. If your organisation is moving toward Fabric - and many Australian enterprises are - these are the connectors you'll use most. The lakehouse and warehouse connectors support Direct Lake mode, which gives you near Import-mode performance without scheduled refreshes. We cover Fabric architecture through our Microsoft Fabric consulting work.

Power Platform connectors cover Dataverse and Power BI dataflows. Dataverse is relevant if you're running Dynamics 365 or Power Apps. The connector works well but be aware of the data volume implications - Dataverse isn't designed to be a data warehouse, and querying millions of rows from it via Power BI can be slow. For Dynamics reporting at scale, landing the data into a SQL database or Fabric lakehouse first is usually the better approach.

Azure Data Sources

The Azure category includes Azure SQL, Synapse Analytics, Analysis Services, Blob Storage, Cosmos DB, Data Explorer (Kusto), Data Lake Storage Gen2, and more.

The practical benefit is that Azure data sources don't need a gateway. Power BI in the cloud talks directly to Azure services. For organisations migrating from on-premise SQL Server to Azure SQL, that's a meaningful operational simplification - one fewer piece of infrastructure to maintain.

A few specifics worth noting. Cosmos DB has two connector versions - use v2, the v1 has known performance issues with larger datasets. Be mindful that Cosmos DB charges per request unit and a busy Power BI report can burn through RUs. Data Explorer (Kusto) is excellent for log analytics and IoT telemetry - it supports both Import and DirectQuery and handles KQL natively. Synapse Analytics works well but watch out for consumption costs if your reports trigger a lot of serverless queries.

Online Services and the "Other" Category

Online Services covers SharePoint Online, Dynamics 365, Salesforce, Google Analytics, and a long tail of SaaS connectors. Many of these are built by third-party partners, not Microsoft, and quality varies. The Salesforce connectors are good. The SharePoint Online List connector is functional but slow at scale. Some beta connectors like GitHub and Mixpanel were built years ago and haven't been updated much since.

The Other category is a grab bag. The most useful connectors here are Web (REST APIs and web pages), ODBC (anything with an ODBC driver), and the Python/R script connectors for when nothing else fits - though scripted connectors come with deployment challenges when publishing to the Power BI service.

PBIDS Files - Useful but Underused

Here's a feature that most Power BI users have never heard of. PBIDS files (Power BI Data Source files) are template files with a .pbids extension that pre-configure a data connection. When someone opens a PBIDS file, Power BI Desktop launches, prompts for credentials, and drops them straight into the Navigator for that data source. No hunting through Get Data, no configuring connection strings.

You create them by going to File > Options and settings > Data source settings, selecting a data source, and clicking "Export PBIDS." Or you can write them by hand - they're just JSON files with a specific structure.

Think about onboarding. A new analyst joins the team and needs to build reports against your data warehouse. Instead of a document explaining "open Power BI Desktop, click Get Data, select SQL Server, enter this server name..." you hand them a PBIDS file. Double-click, enter credentials, start building.

We use PBIDS files for clients with standardised data sources that multiple report authors connect to. It removes mistyped server names, wrong database names, incorrect authentication settings. Small thing, but small things add up when you have 20 people building reports.

The limitation is that each PBIDS file supports only one data source. You can't bundle multiple connections into a single file. For reports that combine data from several sources, it's less helpful. But for the common case of "connect to the main data warehouse and build something" it works well.

Gateway Considerations for On-Premise Data

If any of your data lives on-premise - SQL Server, Oracle, file shares - you need the on-premise data gateway to schedule refreshes from the Power BI service. It's a Windows service that brokers connections between the cloud and your local data sources.

Keep it on a dedicated VM, not someone's workstation. Set up a gateway cluster with at least two members. Monitor it actively. We've seen organisations lose days of refreshes because a single gateway went down during a Windows update and nobody noticed.

If you're evaluating a move to Azure-based data sources, gateway elimination is one of the strongest operational arguments. Cloud-to-cloud connections just work. Our Power BI consultants regularly help organisations plan that migration.

Tips for Choosing the Right Connector

After years of connecting Power BI to everything under the sun, here's what we tell clients.

Use native connectors over generic ones. If there's a dedicated SQL Server connector, use it instead of ODBC. Native connectors are optimised for the source - they support query folding, handle data types correctly, and get updates from Microsoft. ODBC is the fallback when nothing else fits.

Check connector maturity. Look for whether the connector is GA (generally available) or still in beta/preview. GA connectors have full Microsoft support. Beta connectors might work fine, but you're on your own if something breaks. For production reports, GA connectors only.

Test with realistic data volumes. A connector that works perfectly with 10,000 rows might struggle with 10 million. We've seen promising proof-of-concept reports completely fall apart when pointed at real data volumes.

Plan your authentication upfront. Every connector has authentication options - Windows, Basic, OAuth, Service Principal, API Key. The method you choose in Desktop needs to work in the Power BI service when you publish. This catches people out regularly, especially with OAuth tokens that expire.

Think about the full pipeline, not just the connector. What happens when the source schema changes? When the API version gets deprecated? Dataflows, staging databases, or a proper Fabric-based data platform can insulate your reports from source-level changes.

The Power BI connector ecosystem is mature. Microsoft has been building it for over a decade and it covers almost everything. The challenge for Australian businesses isn't finding a connector that works - it's designing a data architecture where the connectors, refresh schedules, gateways, and security model all fit together properly.