1.1 — Connect to Data Sources, Credentials and Privacy Levels
PL-300 Domain 1 · Skill 1.1
What You Need to Know
Every Power BI report starts with Get data in Power BI Desktop (or in the service, for dataflows and web modeling). You pick a connector, sign in, choose tables in the Navigator, and then either Load the data as it is or Transform data in the Power Query Editor.
Common connectors and what they need
| Source | Typical connector | Notes |
|---|---|---|
| Excel workbook, CSV, text | File connectors | A local file path breaks after publishing unless a gateway can reach it. A file in SharePoint or OneDrive for work or school is a cloud source |
| Many files with the same layout | Folder or SharePoint folder | Combine builds a sample file query and a function that runs on every file |
| SQL Server, Azure SQL Database | SQL Server database | Supports Import and DirectQuery. You can paste a native SQL statement under Advanced options |
| SharePoint list, Dataverse, Salesforce | Online services connectors | Signed in with an organizational account |
| Fabric lakehouse, warehouse, semantic model | OneLake catalog | Lists Fabric items you have access to |
| Web page or API | Web | Anonymous, Basic, Web API key or organizational account |
Connecting to a shared semantic model
When you choose an existing Power BI semantic model (Get data > Power BI semantic models or the OneLake catalog), Desktop makes a live connection:
- The report holds no data and no Power Query. All modeling stays in the published semantic model, so one model can serve many reports.
- You need Build permission on the semantic model (lesson 4.5).
- You can still add report-level measures without changing the shared model.
- To add your own tables, choose Make changes to this model. This turns the live connection into a composite model that uses DirectQuery to the semantic model. The tenant setting Allow DirectQuery connections to Power BI semantic models must be on.
Credentials
Power BI stores credentials per data source, not per report.
- Desktop: File > Options and settings > Data source settings. Choose Current file or Global permissions, select a source, then Edit Permissions to change the credential type or Clear Permissions to sign in again.
- Service: credentials for a published semantic model are entered in the semantic model's Settings > Data source credentials, or in the gateway connection (lesson 4.4). A first publish never carries Desktop credentials to the service.
- A source's address can be changed in Data source settings > Change Source, or by using a parameter in the Source step (lesson 1.3).
Privacy levels
Privacy levels stop Power Query from sending data from one source to another while it combines them (for example, query folding a filter value from a private Excel file into a web API URL).
| Level | Meaning |
|---|---|
| Private | Sensitive or confidential. Isolated from every other source, including other Private sources |
| Organizational | Visible to a trusted group. Can be combined with other Organizational sources, but not sent to Public ones |
| Public | Anyone can see it, like open data on the web |
| None | No level set. Power Query treats it as needing one when it has to combine |
- You set the level in Data source settings > Edit Permissions > Privacy Level.
- A Formula.Firewall error ("Query references other queries or steps, so it may not directly access a data source") means Power Query blocked a combination. Fix it by setting correct levels, or by restructuring so each source is read in its own query before they're combined.
- Options > Privacy > Ignore the privacy levels can speed up refresh in Desktop, but it applies only on that computer, and the setting can expose data. Use it only when every source is trusted.
Key Concept: Credentials decide whether Power BI can read a source. Privacy levels decide which sources may see each other's data while queries are combined. The two settings live side by side in Data source settings, and the exam mixes them up on purpose.
Exam Traps
- Expecting a live connection to a shared semantic model to let you edit Power Query or add tables (it doesn't, until you convert it to a composite model)
- Connecting to a shared semantic model with only Read permission (Build is required)
- Setting every source to Public to make a Formula.Firewall error go away (this can leak private data)
- Assuming credentials typed in Desktop are published with the report (they're entered again in the service)
- Using a local file path for a report that must refresh in the service without a gateway
Check your understanding
Practice Scenario
A report merges a SQL Server table of customers with an Excel workbook of customer notes on a company file share. Both hold internal company data that must never be sent to outside sources. Refresh fails because the sources' privacy levels can't be used together. Which privacy levels should you set?
Log in to save your progress.
Sources
- Data sources in Power BI Desktop, Microsoft Learn
- Connect to semantic models in the Power BI service from Power BI Desktop, Microsoft Learn
- Power BI Desktop privacy levels, Microsoft Learn
- Behind the scenes of the data privacy firewall, Microsoft Learn