Power BI Data Sources - What Works Well and What to Watch Out For

One of the first questions we get from new Power BI clients is "can it connect to our data?" The answer is almost always yes. Power BI has connectors for hundreds of data sources - SQL databases, cloud services, flat files, APIs, you name it. Microsoft maintains a full list in their Power Query connectors documentation, and it's genuinely extensive.

But "can it connect" and "should it connect that way" are very different questions. The connector you choose and how you configure it has a big impact on report performance, data freshness, and how much maintenance headache you're signing up for. We've spent the last few years helping Australian organisations get this right, and there are patterns we see over and over.

The Three Ways Power BI Connects to Data

Before getting into specifics, it helps to understand the three connection modes. Each connector in Power BI supports one or more of these, and the one you pick changes how your report behaves.

Import pulls a snapshot of your data into Power BI's in-memory engine. Your data gets compressed, indexed, and stored inside the .pbix file (or the Power BI service, once published). Queries run fast because they hit this local copy, not your source system. The downside is that your data is only as fresh as your last refresh - which could be scheduled every few hours or triggered on demand.

DirectQuery sends every query straight to the source database in real time. No data is cached in Power BI. You always see current data, but every slicer click, every page load, fires a query against your source. If your source is slow or under heavy load, your reports will be slow too.

Live Connection is specific to Analysis Services (Azure or on-premise) and Power BI datasets/semantic models. It's similar to DirectQuery but delegates all the data modelling to the source. You can't add calculated columns or modify the model in Power BI Desktop - you're just building a report on top of an existing model.

Most connectors support Import. Fewer support DirectQuery. You can check what each connector supports in the Power Query documentation - look for the "Capabilities supported" section on each connector page.

What We Actually Recommend for Common Scenarios

Here's what we've found works well in practice across Australian organisations.

SQL Server and Azure SQL

This is the bread and butter. Both Import and DirectQuery are fully supported, performance is solid either way (assuming your database is properly indexed), and the connector is mature and reliable.

For most reporting scenarios, Import with scheduled refresh is the right call. You get the best query performance, and refreshing every few hours is fine for 90% of business reporting use cases. Nobody needs their monthly revenue dashboard to update every 30 seconds.

DirectQuery makes sense when you genuinely need real-time data - operational dashboards for a warehouse floor, for instance - and your SQL Server can handle the query load. We always run performance benchmarks before committing to DirectQuery. We've seen it work brilliantly on a well-tuned Azure SQL database, and we've seen it fall over on an overloaded on-premise server running through a gateway.

Excel and CSV Files

They work. They're everywhere. And they're usually the starting point for Power BI projects because that's where the data lives initially.

Our honest take: Excel is fine for prototyping and small-scale reporting. It becomes a problem when you have 15 people editing a shared Excel file on SharePoint and your Power BI report keeps breaking because someone added a column or renamed a sheet. If your data source is an Excel file that someone maintains manually, plan to move that data into a proper database as soon as the report proves its value. The report is only as reliable as its data source.

CSV files have a similar story. Great for one-off data loads or files that come from automated exports. Terrible for anything where the format might change without warning.

SharePoint Lists

SharePoint as a data source is... fine, up to a point. The connector works, and for small lists (under 5,000 items), it's perfectly reasonable. Beyond that, you start hitting SharePoint's throttling limits and the connector can be painfully slow.

We've had clients with SharePoint lists containing 50,000+ items trying to use them as a primary data source for Power BI. It doesn't scale well. If your SharePoint list has grown past a few thousand rows, it's time to move that data into SQL or Dataverse.

Dataverse (Power Platform)

If you're already in the Microsoft Power Platform ecosystem - Power Apps, Dynamics 365 - Dataverse is a natural data source. The connector is well-maintained and supports DirectQuery, which matters for Dynamics reporting where users expect current data.

One gotcha: Dataverse has its own security model (business units, security roles, etc.) and Power BI has RLS. They don't automatically align. You need to think about whether your Power BI security layer matches your Dataverse permissions, or whether you're accidentally exposing data that users can't see in the source system. We covered RLS in detail in our Power BI row-level security guide.

APIs and Web Sources

Power BI can connect to REST APIs through the Web connector or custom connectors. It works for pulling data from SaaS platforms, government data portals, or internal APIs.

The reality is that API connectors are often brittle. APIs change, rate limits kick in, authentication tokens expire. For production reports, we prefer to land API data into a database first (using Azure Data Factory, Power Automate, or a custom ETL job), then connect Power BI to the database. It's an extra step, but it means your report doesn't break at 6am because an API returned an unexpected response.

The Gateway Question

If your data source is on-premise - a SQL Server in your data centre, a file share on your network, an Oracle database - you need a data gateway. The on-premise data gateway is a Windows service that acts as a bridge between Power BI in the cloud and your local data sources.

Gateways work well when they're properly set up and maintained. They cause headaches when they're not. Here's what we've learned:

Don't run the gateway on someone's desktop. Install it on a dedicated server (or VM) that stays on 24/7. We've seen organisations where the gateway runs on a developer's laptop, and scheduled refreshes fail every time they close the lid.

Plan for gateway clusters. A single gateway is a single point of failure. If it goes down - Windows update, hardware issue, whatever - all your scheduled refreshes stop. Gateway clusters give you redundancy. Set up at least two gateway members for production workloads.

Monitor gateway health. The gateway has logging, but most organisations don't look at it until something breaks. Set up alerts for gateway offline events and failed refreshes. You want to know about problems before your CEO's morning dashboard is showing yesterday's data.

Authentication matters. Different data sources use different authentication methods through the gateway. Windows authentication, basic auth, OAuth - make sure you configure the right method and that the gateway service account has appropriate access to your data sources.

If you're moving to Azure-based data sources (Azure SQL, Azure Synapse, Fabric Lakehouse), you can bypass the gateway entirely since these are cloud-to-cloud connections. That's one less piece of infrastructure to manage, and it's a strong argument for migrating your data platform to the cloud. We help organisations with this transition through our Microsoft Fabric consulting work.

Dataflows - An Underappreciated Middle Layer

Power BI dataflows let you define data transformations in the Power BI service (or in Fabric) and reuse them across multiple reports. Think of them as a shared ETL layer that sits between your raw data sources and your reports.

We're fans of dataflows for a few reasons:

They centralise transformation logic. Instead of each report having its own Power Query transformations, you define them once in a dataflow. When the business logic changes, you update it in one place.

They reduce gateway load. The dataflow handles the connection to the source and stores the transformed data. Reports then connect to the dataflow output, which doesn't require a gateway.

They're good for non-technical data owners. Power Query's visual interface is more accessible than writing SQL. A finance team can maintain their own dataflow that cleans and shapes their data, and the BI team connects reports to it.

The limitation is that dataflows add another layer of scheduling and monitoring. You have to make sure the dataflow refreshes before the reports that depend on it. It's not hard, but it's another thing to manage.

Choosing the Right Approach

Here's our quick decision framework:

If the data is in the cloud and you just need standard reporting - Import mode, scheduled refresh. Simple, fast, reliable.

If you need real-time data and your source can handle it - DirectQuery, but benchmark first.

If you have multiple reports using the same data - Consider a dataflow or Fabric lakehouse as an intermediate layer.

If your data is on-premise - Gateway with Import mode for most scenarios. Plan for a gateway cluster. Consider migrating to cloud data sources when the opportunity arises.

If you're connecting to APIs - Land the data in a database first, then connect Power BI to that.

The connector landscape in Power BI is mature. Most of the problems we see aren't with the connectors themselves - they're with how organisations architect their data pipelines around them. Getting that architecture right from the start saves you from a lot of rework down the line.

If you're planning a Power BI deployment or struggling with your current data connections, our Power BI consultants can help you design a data architecture that scales. And if you're looking at broader data platform modernisation - moving to Fabric, consolidating data sources, building a proper analytics foundation - that's something we do through our business intelligence practice.