Power BI Advanced Series · Module 1 — Lesson 2 · by Raushan Ranjan, MCT
Why Multi-Source Data Is the Real Enterprise Problem
Your CEO wants a single dashboard. Your sales data is in SQL Server. Inventory targets live in a SharePoint Excel file. Customer churn scores are in Azure SQL. Pipeline data is in Dynamics 365. No single source has the full picture — and manually copying between them every morning isn't a strategy, it's a time bomb.
This is the problem Power BI's data connectivity system was designed to solve. In this lesson you'll learn exactly how to pull from each source type, which connectivity mode to pick, and the four mistakes that silently break enterprise reports.
- Import mode is fastest for reports but requires scheduled refresh
- DirectQuery sends live queries — performance depends on source speed
- Live Connection reuses a published Power BI dataset (no duplicate modelling)
- Power Query is the transformation layer before data enters the model
The Universal Translator Analogy
Think of Power BI as a universal translator for business data. Just as a UN interpreter converts French, Mandarin, and Arabic into English without changing what was said, Power Query converts SQL rows, Excel ranges, JSON payloads, and OData feeds into a single unified table format — without altering the source systems. Your SQL Server doesn't know Power BI spoke to it. Your SharePoint file stays exactly as Finance left it. Power BI just listens, translates, and combines.
A national retailer with 500 stores needs a weekly executive dashboard. Their data landscape:
Power BI connects to all four simultaneously. The analyst sees one model. Finance sees their targets next to actuals. Operations sees supplier delays against inventory. One dashboard. Four systems. Zero manual merging.
Source Categories: What Connects to What
| Category | Examples | When to Use |
|---|---|---|
| Files | Excel, CSV, JSON, PDF, XML | Ad-hoc data from analysts; one-time imports |
| Databases | SQL Server, PostgreSQL, Oracle, MySQL, Access | Core operational data; scheduled refresh or DirectQuery |
| Azure / Cloud | Azure SQL, Blob Storage, Synapse, Databricks | Large-scale cloud workloads; modern data platform |
| Online Services | SharePoint, Dynamics 365, Salesforce, Google Analytics | SaaS platforms with OAuth connectors |
| Power Platform | Power BI Datasets, Dataflows, Dataverse | Reuse certified models; avoid duplicate DAX |
| Other | Web, OData, ODBC, OLEDB, Blank Query | APIs, legacy systems, custom M queries |
Connectivity Modes: Import vs DirectQuery vs Live Connection
| Mode | Data Location | Refresh | Best For | Limitation |
|---|---|---|---|---|
| Import | Copied into .pbix / dataset | Scheduled (up to 8×/day free, 48×/day Premium) | Fast visuals, complex DAX, small-to-medium data | Data is never newer than last refresh |
| DirectQuery | Stays at source | Live — each visual interaction queries source | Large tables, near-real-time, governance requirement | Limited DAX functions; slow if source is slow |
| Live Connection | Published Power BI / SSAS model | Inherits from source dataset | Multiple reports reusing one governed model | No local transformations; model is read-only |
For a deeper dive, see the dedicated article: Power BI Data Connectivity Modes Explained →
- Hardcoded file paths in Excel/CSV connectors.
C:UsersRaushanDesktopsales.xlsxworks on your machine, breaks on every other machine and in the cloud service. Fix: use a parameter or relative path, or move the file to SharePoint. - Using DirectQuery for small, static tables. If your product dimension has 500 rows and never changes intraday, Import is always better. DirectQuery on small tables adds latency with zero benefit.
- Skipping scheduled refresh after publishing. You connect, publish, share the link — and forget to configure refresh. Three days later your CEO is looking at last week's data. Always configure the gateway and refresh schedule before sharing.
- Using the Web connector for REST APIs. The Web connector works for simple URLs, but for APIs that need auth headers, pagination, or POST requests, write a proper M function in Power Query or use a custom connector. The Web connector will quietly truncate or fail on page 2.
- Power BI connects to 100+ sources — categorised as files, databases, cloud, online services, and Power Platform
- Import mode is fastest but has data freshness lag; DirectQuery is live but source-speed dependent
- Live Connection lets multiple reports share one published dataset — the right choice for enterprise governance
- Always prefer native connectors over ODBC to enable query folding and better performance
- Avoid hardcoded file paths and always configure refresh before publishing to colleagues
Q1. Your source table has 50 million rows and your business needs data no more than 15 minutes old. Which connectivity mode should you choose?
Show Answer
DirectQuery. Import mode can refresh at most 48×/day on Premium (every 30 min). For 15-minute freshness with 50M rows, DirectQuery is the correct choice — as long as your source (e.g., Azure SQL, Synapse) can handle the query load.
Q2. Three different report authors each build separate .pbix files connecting to the same SQL Server sales table. What is the problem with this approach, and what is the better solution?
Show Answer
Problem: Each author defines their own measures, potentially with inconsistent logic (e.g., different revenue calculations). Three datasets duplicate storage and refresh load.
Solution: One author builds and publishes the certified dataset. The other two use Live Connection to that dataset, so all three reports share identical logic and a single refresh.
Q3. A colleague shares a .pbix file with a CSV connector. When you open it you get a "File not found" error. What is the most likely cause?
Show Answer
The connector uses a hardcoded local file path (e.g., C:UsersJohnDocumentsdata.csv) that exists only on their machine. Fix: move the file to a shared location (SharePoint, OneDrive, network path) and update the source path, or use a Power Query parameter so it can be changed without editing the query.
- RR Skillverse: Power BI Visual Cheat Sheets →
- MS Learn: Data sources in Power BI Desktop →
- MS Learn: About using DirectQuery in Power BI →
- MS Learn: Data refresh in Power BI →
Next in the series: Diagnosing and Resolving Data Import Errors →