Connecting Power BI Desktop to Data Sources - Practical Lessons From Client Projects

Every Power BI project starts the same way. You open Power BI Desktop, click "Get data", and stare at a list of data source options that has grown to look like a small phone book. SQL Server. SharePoint. Salesforce. Snowflake. Web. Excel. About 300 others. For people new to Power BI, this menu is intimidating. For experienced developers, the choice of connector is one of the first decisions that determines whether the report will be easy or painful to maintain.

I want to talk about the practical side of data connections in Power BI Desktop. Not the marketing version where every connector works perfectly. The real version, where some connectors are excellent, some are buggy, and the difference between getting it right and getting it wrong shows up months later when the data refresh fails at 3am.

The Basic Versus Advanced Decision

When you connect to a Web source, Power BI gives you Basic and Advanced options. Most people pick Basic and move on. They're usually right to do so. Basic mode takes a single URL and gives you the page contents. Done.

Advanced mode is where things get interesting. You can build the URL from multiple parts, add HTTP request headers, and specify command timeouts. This matters when:

• You're calling an API that needs an authentication header
• The URL needs to change dynamically based on parameters
• The endpoint is slow and you need a longer timeout
• You need to construct the URL from a configuration table

We worked with a property services client last year who needed to pull data from about 15 different state government property datasets. Each had a slightly different URL pattern. Instead of creating 15 separate queries, we used Advanced mode with a parameter table to construct URLs dynamically. The whole thing collapsed into one parameterised query that handled all 15 sources.

The Basic option is for "I just want this data". The Advanced option is for "I want this data and I'm thinking about maintenance, parameterisation, and reuse". You'll use Basic 80 percent of the time. The other 20 percent is where Advanced earns its keep.

What Happens After You Connect

Power BI Desktop opens the Navigator window with whatever it found. For a database, this is a list of tables. For a web page, it's any tables Power BI managed to detect. For a SharePoint site, it's the lists and document libraries.

Two choices appear at the bottom of Navigator. Load. Transform Data. The decision between these matters more than people realise.

Click Load and the data goes straight into your model. Fast. Easy. Wrong almost every time.

Click Transform Data and you go into Power Query Editor first. This is where you should be doing your shaping, type conversions, column renames, and filtering before the data ever hits the model. The difference between a clean, well-shaped model and a dirty, frustrating one usually comes down to whether the developer spent enough time in Power Query Editor.

The default behaviour in Power BI Desktop pushes you toward Load. Resist it. Almost every serious Power BI report benefits from at least some transformation before loading. Even something as simple as removing columns you don't need will make your model smaller and faster.

A Worked Example - The Retirement Data Problem

Microsoft's own documentation uses an example I find amusing. You're planning your retirement and want to find the best places to retire in the US. There's a web page from Kiplinger with that data. Connect to it via Web. Pull the table. Done.

What actually happens in practice illustrates the problem with naive data loading.

The Rank column comes through as text, not numbers. This is fine until you try to sort by rank or do any calculation. The values include things like "(same as 2024)" appended to the number. The columns have inconsistent capitalisation. There are footnote markers in some cells.

Power BI doesn't know any of this. It just pulled the data faithfully from the source. Making it usable is your job.

The fix is simple but instructive. Right-click the Rank column. Replace values. Get rid of "(same as 2024)". Right-click again. Change type to Whole Number. Repeat for any other columns that need attention.

Every change you make gets recorded in the Applied Steps panel on the right. Refresh the data tomorrow and every step runs again, automatically. This is the underrated power of Power Query - your transformations are repeatable, auditable, and refreshable. Excel users coming to Power BI sometimes try to do their data cleaning manually each time. The Power Query approach takes longer to set up but pays dividends every refresh.

The Connectors That Just Work

After many years of building Power BI reports for clients, I have strong opinions about which connectors are reliable and which are still rough.

SQL Server, Azure SQL Database, Synapse Analytics. Rock solid. These are Microsoft's home turf. Performance is good. Query folding works. DirectQuery is well supported.

Excel files. Reliable but with caveats. Stored on SharePoint or OneDrive? Fine. Stored on a network share? Plan for refresh issues when the file is locked by someone editing it.

Web scraping. Works for static HTML tables on stable websites. Don't build production reports on websites you don't control. The HTML changes, your report breaks, you find out a month later when someone notices the numbers haven't updated.

SharePoint Online lists. Works well for small to medium datasets. Performance degrades sharply above about 100,000 rows. If you have a SharePoint list with millions of rows, you have a different problem to solve.

Microsoft Dataverse. Good integration but slow connector. Plan your refresh schedule accordingly.

The Connectors That Need More Care

Salesforce. Works, but the connector has limits. We've had to fall back to the Salesforce REST API for clients with very large data volumes or complex security models.

Snowflake. The connector is solid but you need to think carefully about how queries fold. Get this wrong and you'll pull millions of rows back to your local machine when you should be aggregating in Snowflake first.

Generic ODBC connections. They work, but performance varies wildly depending on the driver and source system. If you have a choice between an ODBC connection and a native connector, choose the native connector every time.

REST APIs through Web.Contents. Powerful but requires understanding how to handle pagination, authentication, and error responses. This is where Power Query starts feeling like a programming language rather than a configuration tool.

DirectQuery Versus Import - The Decision That Defines Your Project

When you connect to certain data sources, you'll get a choice between Import and DirectQuery. This is one of the most consequential decisions in a Power BI project.

Import loads data into Power BI's in-memory engine. Fast queries. Compressed storage. Refreshes happen on a schedule. Works with almost all features.

DirectQuery sends queries to the source database in real time. No data is stored in Power BI. Reports are always current. But query performance depends entirely on the source system, and some features don't work.

For most projects, Import is the right choice. The exceptions are when data volumes are too large for memory (a tens of billions of rows kind of problem) or when you genuinely need real-time data and your source can handle the query load.

We worked with a manufacturing client who insisted on DirectQuery because they wanted "live" data on a production dashboard. After two weeks of investigation, it turned out their data only actually changed every 15 minutes. Switching to Import with a 15-minute refresh schedule gave them the same business outcome with much better report performance.

The honest answer most consultants won't give you upfront. If you're not sure whether to use Import or DirectQuery, you should use Import. The cases where DirectQuery is the right choice are real but uncommon, and you'll know if you're in one of them.

Building From Connection to Report

Once your data is connected, transformed, and loaded, the Report view in Power BI Desktop is where you build visuals. Drag fields onto the canvas. Add chart types. Configure formatting.

This is where Power BI feels like a polished product. The Report view has matured impressively over the years. The variety of visuals, the formatting options, the cross-filtering and drill-through capabilities all work well.

The trap here is spending all your time on the Report view because it's the fun bit. We see Power BI reports built by clients themselves that have gorgeous visuals sitting on top of a data model that's a mess. Six months later, when someone needs to add a new measure or modify a calculation, the whole thing falls over.

The proportion of time we spend on a typical Power BI project breaks down something like this:

• 35-50% on data preparation in Power Query
• 20-30% on data modelling and DAX
• 15-25% on report design and visuals
• 10-15% on testing, deployment, and refresh configuration

If you're spending most of your time on the visuals, your data model probably needs more work.

A Note on Power BI Service Versus Desktop

Power BI Desktop is where you build. Power BI Service is where reports run for end users. The connections you set up in Desktop need to work in Service too, which is a frequent source of friction.

Some connectors that work in Desktop don't work in Service without a data gateway. Some authentication methods don't translate. Some refresh schedules behave differently. Test your refresh in Service early, not just at the end of the project.

For Australian organisations, this is especially relevant when data is hosted on-premises or in a regional Australian Azure datacenter. Network paths matter. Latency matters. We've seen Power BI deployments that worked perfectly in development environments fall over when deployed because of network access issues from the Power BI Service to on-premises data.

Where to Go From Here

Power BI Desktop is one of the most capable analytics tools available, and connecting to data is just the first step. The depth of what's possible with the Power Query Editor, DAX, and the data modelling capabilities is significant.

For organisations getting started, the right pattern is usually to learn by doing. Build something small. Connect to a familiar data source. Get a working report end to end. Then iterate. Reading documentation is helpful but the muscle memory comes from building.

For organisations doing this seriously across many reports and users, the conversations get more complex. Governance. Workspace structure. Dataflow strategy. Premium capacity decisions. These are the kinds of questions we work on regularly with Power BI consulting engagements for Australian clients.

If you're working through Power BI projects and would like to talk through the data connection approach or report architecture, get in touch. Whether you're building your first dataset or designing for an enterprise rollout, the foundations matter.

Reference: Connect to data sources in Power BI Desktop