Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 56 ยท Getting Data

Data Source Settings, Privacy Levels and Parameters

Manage Power BI data source credentials, understand privacy levels and the Formula.Firewall error, and use parameters for file paths and servers.

Connecting to a source is only half of the job. Power BI also has to remember how it is allowed to sign in, decide whether data from one source may be combined with data from another, and let you move a report from your laptop to production without editing every query. Three features handle these three jobs: data source credentials, privacy levels and parameters.

These are settings and M code in the real tool, so they are described read-only in this lesson. The Python stand-ins show the logic behind two of them, a parameter-driven loader and a simplified privacy check, so you can see the idea at work. They are teaching models of the behaviour, not copies of Power BI's engine.

Credentials and Data source settings

When you connect to a source, Power BI asks for credentials once and stores them on your computer (not inside the .pbix file). The type depends on the source:

  • Windows: your current Windows sign-in, or alternate Windows credentials. Common for on-premises SQL Server.
  • Database (or Basic): a user name and password defined in the database itself.
  • Organizational account: a Microsoft Entra ID (formerly Azure AD) sign-in, used by Azure services, SharePoint and Dataverse.
  • Anonymous: no sign-in, for public files and websites.
  • Web API: an API key supplied in a header or query string.

To review them, use File > Options and settings > Data source settings. The dialog has two scopes: Data sources in current file (the ones this report uses) and Global permissions (every source Desktop remembers on this machine). From here you can Edit Permissions to change a password or sign in as someone else, Clear Permissions to forget one, and set the privacy level of a source.

If a refresh suddenly fails with "credentials are invalid" or a sign-in prompt appears again, the password probably changed or expired. Open Data source settings, choose Edit Permissions and enter the new one. Never save passwords in M code or in a file next to the report.

In the Power BI service credentials are separate. After you publish, you open the semantic model's Settings, find Data source credentials and sign in again there, so the cloud can refresh without your laptop. For on-premises sources this also involves a gateway, covered in Scheduled Refresh and Gateways.

Privacy levels

Imagine a query that merges a confidential HR table from SQL Server with a lookup file that came from a public website. If Power BI tried to be clever and pushed the HR rows into a query to that website, private data could leak. To prevent that, Power Query classifies each data source with a privacy level:

Three privacy levels

Private Sensitive. Never shared with another source.
Organizational Visible to a trusted group only.
Public Open to anyone, safe to share.

When in doubt, choose the stricter level.

1 / 2

The rough rules are: Private data is never sent to another source, Organizational data may be shared only with other Organizational sources, and Public data can be shared with anyone. You set the level per source in Data source settings, or Desktop asks the first time two sources meet in one query.

The checks live in the part of the engine often called the data privacy firewall. When a query would mix two sources, the firewall makes sure data flows according to the levels. A well-known consequence is this error:

Formula.Firewall: Query 'Orders' (step 'Merged') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

It usually means a single step both reads a data source and refers to another query, which could expose data across levels. The standard fix is to split the work: one query only loads the source (a staging query), and a second query combines the staged queries. You can also set the right privacy levels, and in a closed environment where you fully trust every source, you can choose Ignore the Privacy levels in the file's Privacy options. Doing that removes the protection, so only do it deliberately, and understand the risk.

Privacy and query folding

Privacy levels also affect performance. When two sources have different levels, the firewall may partition the data so that each source is evaluated on its own. A step that could have been sent to the database as SQL (this is query folding, explained in Query Folding, Incremental Refresh and Query Performance) may no longer fold, and Power Query fetches the rows and processes them locally. A merge that is quick when both sources are marked Organizational can become slow if one is Private. If a refresh got slower after you changed privacy levels, this is the first thing to suspect.

A simplified privacy check

The stand-in below encodes the rules from this section as a function. It is deliberately simple: the real firewall works on query partitions and is more subtle, but the function shows how levels decide whether combining is allowed and where the work would be forced to happen.

Python Stand-in: a simplified privacy check

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the output, the two Organizational databases can cooperate, while anything involving the Private source is isolated, which in the real tool means local processing and a possible loss of folding.

Parameters

A parameter is a named value that your queries can use. They stop you from hard-coding things that change: a file path, a server name, a start date, a row limit. Create one in the Power Query Editor with Manage Parameters > New Parameter and give it a name, a type (text, decimal number, date, true/false and so on), and optionally a list of suggested or allowed values. A parameter named DataFolder of type text, with the current value C:\Data\, appears in the Queries pane and as the M value "C:\Data\".

You use it by name inside another query:

M
// A file path built from a parameter
Source = Csv.Document(File.Contents(DataFolder & "sales.csv"), [Delimiter = ","])

// A server and database from parameters
Source = Sql.Database(ServerName, DatabaseName)

// Limit rows while developing (RowLimit is a number parameter)
Limited = Table.FirstN(Orders, RowLimit)

Parameters are useful in many situations:

  1. Moving between environments. Develop against dev-sql, publish with prod-sql. You change one parameter, not twenty steps. In the service you can set the values in the semantic model's Settings > Parameters section without reopening Desktop.
  2. Sharing a file. A colleague opens your .pbix, changes DataFolder to their path and refreshes.
  3. Faster development. A RowLimit of 1000 keeps refreshes quick while you shape data. Set it very high (or remove the step) for the final version.
  4. Templates. A .pbit template asks for parameter values when opened.
  5. Incremental refresh. The RangeStart and RangeEnd parameters that drive it are ordinary date/time parameters with special names (see the lesson on incremental refresh).

Remember that a parameter holds a plain value; it is not a place for secrets. Passwords belong in the credentials system.

A parameter-driven loader, in Python

The stand-in below mimics the idea. The settings dictionary plays the role of parameters, and the loader never mentions a file name or limit directly. Changing the settings changes the behaviour, with no code edits.

Python Stand-in: parameters drive the query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Switching from the development to the production settings changes the data without touching the loader, which is exactly what parameters give you in Power Query.

A quick troubleshooting guide

  • "Credentials are invalid" or sign-in prompts. Open Data source settings and edit permissions; in the service, re-enter credentials in the model's settings.
  • "Formula.Firewall" error. Separate the data loading from the combining in different queries, and check privacy levels.
  • Refresh worked on my laptop but not in the service. The path or server is not reachable from the cloud; use a gateway and a shared or parameterised location.
  • A query became slow after combining two sources. Check whether privacy levels stopped folding.
  • A colleague cannot open the file. Hard-coded path; replace it with a parameter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where do you review and change the stored credentials for the data sources a report uses?

    Data source settings lists sources in the current file and your global permissions, where you can edit or clear credentials and set privacy levels.
  2. 2. A query merges a Private database with another source. What does the data privacy firewall do?

    Private data is not shared with other sources. The engine evaluates each source separately and combines the results locally, which can stop query folding and slow things down.
  3. 3. Which is a typical good use of a parameter?

    Parameters hold values that change between environments or users, such as paths and servers. Passwords belong in the credentials system.
  4. 4. You see a Formula.Firewall error. What is the standard fix?

    The error means one step both reads a source and references another query. A staging query that only loads each source, plus a second query that combines them, resolves it.

Summary

  • Credentials are stored per data source and managed in Data source settings; the service stores its own copy for refresh.
  • Privacy levels (Private, Organizational, Public) tell the firewall how sources may be combined; stricter levels can disable query folding.
  • The Formula.Firewall error is fixed by separating source-loading queries from combining queries.
  • Parameters hold values such as paths, servers and row limits so queries do not contain hard-coded values; the service can edit them after publishing.
  • The Python stand-ins illustrated the logic of parameters and a simplified privacy check; the real tool's firewall is more detailed.

Next chapter, start cleaning and reshaping data in The Power Query Editor.