Launch Deal · 50% off 1st month ·

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?

Report issue

Log in to save your progress.