Connecting to Data Sources
Learn how to connect Power BI to SQL Server, Excel, SharePoint, the web, and shared semantic models — plus how to manage credentials, privacy levels, and parameters.
Where does your data live?
Imagine you’re making a smoothie.
You need fruit from the fridge, ice from the freezer, and protein powder from the pantry. Each ingredient comes from a different place, but they all end up in the same blender.
Power BI Desktop is your blender. It pulls data from wherever it lives — a SQL database, an Excel file on SharePoint, a web API, a Dataverse table — and combines it into one model you can analyse and visualise.
This module covers how to connect to those sources, manage your login credentials, set privacy levels so data from different sources doesn’t leak into each other, and use parameters to make your connections flexible and reusable.
Common data sources in Power BI
Riley Park at Coastal Fresh (🛒) needs to build a sales dashboard. Her data lives in multiple places:
| Source | What’s There | Connector |
|---|---|---|
| SQL Server | Point-of-sale transactions, 2M+ rows | SQL Server database |
| Excel files | Regional budget targets, updated monthly | Excel workbook |
| SharePoint list | Store locations and manager contacts | SharePoint Online list |
| Web API | Weather data for sales correlation | Web connector |
| Dataverse | Customer loyalty programme | Dataverse |
She opens Home → Get Data and picks the right connector for each source. Power BI asks for a server address, file path, or URL — then shows a preview of the available tables or sheets.
Exam tip: Shared semantic models
Instead of building a model from scratch, Riley could connect to a shared semantic model that her BI team already published to the Power BI service. This is called a live connection — she gets the existing measures, relationships, and security without importing any data.
Use shared semantic models when:
- A curated, trusted semantic model already exists
- You want consistent metrics across reports
- You don’t need to add new tables or modify the model
On the exam, look for scenarios where a “certified semantic model” or “shared semantic model” is available — connecting to it is often the right answer over re-importing. Note: report authors can sometimes extend shared models with local measures, so it’s not always a binary choice.
Credentials and privacy levels
Every data source requires credentials — how Power BI proves you’re allowed to access that data.
| Credential Type | When It’s Used |
|---|---|
| Windows | On-premises SQL Server, file shares |
| Database | SQL authentication (username + password) |
| Microsoft account | Personal OneDrive, consumer services |
| Organisational account | SharePoint, OneDrive for Business, Dataverse, Azure services, Power BI service |
| Anonymous | Public web APIs, open data |
| Key / API key | Third-party services (e.g., weather API) |
You manage credentials in File → Options → Data source settings. If a connection fails, the first thing to check is whether the credentials are correct and current.
Privacy levels
Privacy levels control whether Power BI can send data between sources during query execution. They determine data isolation boundaries.
| Level | What It Means | Example |
|---|---|---|
| Private | Completely isolated — data is never sent to or from other sources | Employee salary data, patient records |
| Organisational | Can exchange data with other Organisational and Public sources, but isolated from Private sources | Internal sales data, SharePoint lists |
| Public | Data can be seen by any source — no isolation restrictions | Weather data, public APIs, government datasets |
Why privacy levels matter on the exam
If Power BI can’t combine two sources, you’ll see a Formula.Firewall error. The fix is to set privacy levels correctly — not to ignore them.
Common exam scenario: “A query fails with a privacy error when merging an internal SQL table with a public web API.” The answer is to set the SQL source to Organisational and the web source to Public — then the merge works because Organisational can combine with Public.
Parameters: make connections flexible
Parameters are variables you define once and use across multiple queries. They make your connections dynamic and reusable.
Nadia Okafor at Prism Agency (📊) builds reports for 30 different clients. Instead of creating 30 separate .pbix files, she uses a parameter called ClientDatabase that switches which database the queries connect to.
Creating a parameter:
- In Power Query Editor → Manage Parameters → New Parameter
- Give it a name (e.g.,
ServerName) - Set the type (Text, Number, Date, etc.)
- Define allowed values or a default
Using a parameter in a connection:
- In the SQL Server connector, replace the hardcoded server name with the parameter
- When someone opens the file, they can change the parameter value without editing the query
Real-world: environment switching with parameters
Kenji Tanaka at Apex Manufacturing (🏭) uses parameters to switch between DEV-SQL-01 and PROD-SQL-01. During development, he points to the dev server. Before publishing to the Power BI service, he switches to production. This avoids accidentally modifying production data while testing.
Knowledge check
Riley at Coastal Fresh needs to combine internal sales data from SQL Server with public weather data from a web API. She gets a Formula.Firewall error. What should she do?
Nadia at Prism Agency creates a Power BI parameter called 'ClientDB' to switch between client databases. Where does she create this parameter?
🎬 Video coming soon
Next up: Storage Modes: Import, DirectQuery and DirectLake — the single most important architecture decision in Power BI.