Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🔌 Module 2 · Connecting to Data Intermediate LESSON 15 / 72

Data Refresh & Fixing Connection Problems

27 min 5 quiz questions 1 practical challenge 5 syllabus topics

Getting data in once is easy. Getting it in reliably, every morning, without anyone touching it, is the part that separates a demo from a working report. This lesson covers how refresh actually works, what a gateway is for, and a diagnostic playbook for the errors you will genuinely encounter.

Lesson 15 of 72 · Connecting to Data 21%
01

Learning Objective

Refresh failures are the most common operational problem in Power BI, and the error messages are frequently unhelpful. Knowing the small number of underlying causes turns a frustrating afternoon into a five-minute fix.

By the end of this lesson you will be able to
  • Explain how refresh differs in Desktop and in the Power BI service
  • Describe what a data gateway does and when one is required
  • Explain privacy levels and why they block query folding and merges
  • Diagnose the six most common refresh errors from their symptoms
  • Combine multiple sources without triggering the formula-firewall error
02

Why This Matters

A pattern that repeats in every organisation. A report is built, demonstrated, praised, and published. Three weeks later someone mentions the numbers look stale. The scheduled refresh has been failing since day two, and nobody was watching.

The cause is almost always one of a handful of things — credentials not configured after publishing, a gateway not installed, a privacy level conflict, or a source path that only exists on the author's laptop. All are preventable in about ten minutes, if you know to look.

Refresh failures are silent by default
The Power BI service does not shout about a failed refresh. Unless someone has configured failure notifications, a broken refresh looks exactly like a working one — the report opens fine and shows the last data that loaded successfully. Setting up notifications is the first thing to do after publishing anything anyone will rely on.
03

Concept

Refresh in two places

Desktop vs the service
Power BI DesktopPower BI service
TriggerYou click RefreshScheduled, or triggered manually, or via API
CredentialsStored per user, per machineStored in the semantic model's settings
On-premises sourcesDirect connectionRequires a data gateway
Cloud sourcesDirect connectionUsually direct, no gateway needed
Frequency limitsNone — refresh as often as you likeGated by licence tier
Failure visibilityAn error dialog you cannot missSilent unless notifications are on
Scheduled refresh frequency limits depend on licence tier and change over time. Verify current limits against Microsoft's refresh documentation rather than relying on a remembered number.

The data gateway

The Power BI service runs in Microsoft's cloud. Your SQL Server sits inside your company network, behind a firewall. The service cannot reach it, and you would not want it to be able to.

The on-premises data gateway solves this. It is a small Windows service installed inside your network that makes an outbound connection to Azure and waits for work. When a refresh is due, the service sends the query through that existing outbound channel; the gateway runs it locally and returns the result. No inbound firewall rule is required.

Two gateway modes
ModeWhat it isUse for
StandardA shared gateway, administered centrally, usable by many users and several services (Power BI, Power Apps, Power Automate)Anything production. This is what you want
PersonalRuns under one user's account, Power BI only, and only works while that machine is onPersonal experiments only. Never for shared reporting
Personal gateway on a laptop is a trap
It works perfectly until the person closes the lid, goes on leave, or leaves the company. Then the refresh stops and nobody knows why. If a report matters to anyone but you, it needs a standard gateway on a server.

When you need a gateway

  • Yes: SQL Server on-premises, a file on a network share, an on-premises SAP or Oracle system, a local Analysis Services instance.
  • No: Azure SQL Database, SharePoint Online, OneDrive for Business, Dataverse, most SaaS connectors — these are cloud-to-cloud.
  • Depends: a file in OneDrive for Business usually does not need one; the same file on a mapped drive letter does — and the mapped drive will not work at all.

Privacy levels and the formula firewall

This is the least intuitive part of Power Query and the source of one of its most confusing errors.

Power Query assigns each data source a privacy level. The purpose is to prevent data leaking between sources: if you merge a confidential customer list with a public web service, a naive implementation might send customer names to that web service as query parameters.

The three levels
LevelMeaning
PrivateMust never be shared with any other source. Most restrictive
OrganizationalMay be shared with other Organizational sources, but not Public ones
PublicMay be shared freely

When Power Query cannot prove that combining two sources is safe, it refuses — and it refuses with the message "Formula.Firewall: Query 'X' (step 'Y') references other queries or steps, so it may not directly access a data source."

Three ways to resolve it

  1. Set compatible privacy levels. File › Options › Current File › Privacy, and Data source settings › Edit Permissions for each source. Two Organizational sources combine happily.
  2. Restructure the query. The firewall objects to a query that both references another query and touches a data source. Splitting it into a staging query that only reads the source, and a second query that only transforms, usually resolves it.
  3. Ignore privacy levels. Options › Current File › Privacy › Always ignore Privacy Level settings. It works, and it also disables the protection.
Think before ignoring privacy levels
It is the fastest fix and it is frequently the right one for a model combining internal sources you already trust. It is the wrong answer if you are genuinely combining confidential data with an external service — that is precisely the case the firewall exists for. Decide deliberately rather than clicking it because the error is annoying.

Note also that the setting has to be configured in the service too; setting it in Desktop alone does not carry over.

Combining multiple sources

A model can draw from as many sources as you like. Practical points:

  • Merges across sources do not fold. Joining a SQL table to a CSV means both are pulled locally and joined in Power Query.
  • Every source needs its own credentials, in Desktop and again in the service.
  • A single gateway can serve several sources, provided each is configured on it.
  • Refresh is only as reliable as its least reliable source. One flaky web endpoint fails the whole refresh.
04

Visual Explanation

How a scheduled refresh actually flows, and where it breaks.

The diagnostic table

Six errors and what they actually mean
SymptomMost likely causeFirst thing to check
"Credentials are required" after publishingCredentials were never configured in the serviceSemantic model → Settings → Data source credentials
"Unable to connect… gateway"No gateway, or the source is not registered on itWhether the gateway is online, and whether that exact server/database is in its source list
Formula.FirewallPrivacy level conflict between sourcesData source settings → Edit Permissions on each source
"The column 'X' of the table wasn't found"The source schema changed — a column renamed or removedOpen the query and find the step referencing it
Timeout on a large tableFolding broken, so everything is being pulled locallyView Native Query on each step (Lesson 3)
Refresh works in Desktop, fails in the serviceA local file path, a mapped drive, or missing gateway configurationData source settings — look for a path only you can reach
The last row is the classic
"It works on my machine" is as true in Power BI as anywhere else, and it nearly always means a path or a credential that exists locally and not in the service. Check Data source settings before publishing anything: if you see a path starting with a drive letter, it will not refresh in the cloud.
05

Power BI Demonstration

Audit your sources before publishing
Home › Transform data › Data source settings. Look at every entry. Any path beginning with a drive letter (C:\, Z:\) will not refresh in the service.
Fix a local path
Either move the file to SharePoint or OneDrive and reconnect, or parameterise the path as covered in Module 1 Lesson 5.
Check credentials for each source
In the same dialog, Edit Permissions shows the credential type and the privacy level for each source.
Set privacy levels deliberately
For internal sources you trust, set both to Organizational. This resolves most firewall errors without disabling protection entirely.
Publish and configure credentials immediately
After Home › Publish, open the service, find the semantic model (not the report), and go to Settings › Data source credentials. Sign in for each source. Do this straight away — it is the most commonly skipped step.
Set up the schedule
Same settings page → Scheduled refresh. Turn it on, set times, and set the time zone explicitly.
Turn on failure notifications
On the same page there is an option to email the owner on refresh failure. Enable it, and consider adding a second contact so a single person's absence does not hide a problem.
Read the refresh history
Semantic model → Refresh history. It shows every attempt, its duration and the full error message for failures. This is the first place to look when something is stale.
Test a deliberate failure
Rename a column in your source file and refresh. Read the error. Then rename it back and confirm recovery. Seeing the error message in a controlled situation makes it far easier to recognise later.
Power BI serviceSemantic modelSettingsData source credentials
Configure credentials before you tell anyone the report exists
The gap between publishing and configuring credentials is where most "the report is broken" reports come from. Publish, configure, schedule, verify one successful refresh — then share the link.
06

Example Dataset

This lesson is conceptual and needs no download — it uses the sample data shown inline above. The next lesson that requires a file will link it here.

You can still browse every dataset in the course from the dataset library.

07

Step-by-Step Exercise

Build a two-source model
Load bwb-retail-sales.csv and bwb-products.csv from a local folder.
Trigger a firewall error deliberately
Create a query that references another query and also reads a data source in the same step — for example, a query that takes a value from the products query and uses it to filter a freshly-read source. Observe the Formula.Firewall message.
Resolve it three ways
First by setting both sources to Organizational. Then by restructuring into a staging query plus a transform query. Then by ignoring privacy levels. Note which you would use in practice and why.
Audit your data source settings
Open Data source settings and list every path. Identify which would fail in the service.
Make the model portable
Parameterise the folder path so the model would work from a shared location.
Simulate a schema change
Rename NetSales to Net_Sales in a copy of the CSV, point the query at it, and refresh. Read the exact error text and note which step it names.
Write the recovery
Fix it in the query rather than in the file — add a rename step, so the model tolerates either name. Consider whether that is a good idea or whether it hides a real problem.
08

Expected Result

What you should observe
StepResult
Firewall errorFormula.Firewall: Query 'X' (step 'Y') references other queries or steps, so it may not directly access a data source
Both sources set to OrganizationalError resolved, protection retained
Restructured into staging + transformError resolved, and the query is arguably clearer
Ignore privacy levelsError resolved, protection disabled for the whole file
Renamed columnThe column 'NetSales' of the table wasn't found, naming the step that references it
Adding a tolerant rename stepRefresh succeeds with either column name
Should you make the query tolerant of both names?
Usually no. A column renamed at source is a change you want to know about — it may signal a wider schema change, and silently accommodating it means the next change goes unnoticed too.

The exception is a source that legitimately alternates between formats, where tolerance is the correct design. Otherwise, let it fail, fix it deliberately, and ask the source owner to tell you before the next change. This is the fail-loudly principle again.
09

Common Mistakes

Publishing and not configuring credentials
The report looks fine because it contains the data you published. Scheduled refresh has never run successfully.

Fix: Configure credentials on the semantic model immediately after publishing, then watch one refresh succeed before sharing the link.
Using a personal gateway for a shared report
Works until the machine is off. Then refresh stops and the failure is invisible.

Fix: Standard gateway on an always-on server for anything anyone else depends on.
Clicking 'ignore privacy levels' reflexively
It removes an annoying error and also removes a real protection, and it has to be set in the service separately.

Fix: Try setting compatible privacy levels first. Reserve the override for models combining sources you have already assessed as safe to combine.
Referencing a mapped drive letter
Z:\shared\data.xlsx is a drive mapping that exists only on your machine. The gateway sees nothing.

Fix: Use the full UNC path (\\server\share\data.xlsx) or move the file to SharePoint or OneDrive.
Not turning on refresh failure notifications
The refresh has been failing for a fortnight and everyone has been reading stale numbers.

Fix: Enable notifications on every semantic model that anyone relies on, and send them to more than one person.
10

Professional Tip

Put the last refresh time on the report itself
Add a measure and display it on every page:

Last Refresh = "Data as at " & FORMAT(MAX('Refresh Log'[Refreshed]), "dd MMM yyyy HH:mm")

The simplest implementation is a one-row query that captures DateTime.LocalNow() at refresh time — it evaluates when the model refreshes, so it records exactly that.

This single line converts silent staleness into something a reader notices immediately. If the report says "Data as at 3 March" and today is 20 March, someone will say something — which is the entire point. It is the cheapest possible insurance against the failure mode this lesson is about.

Two supporting habits:

  • Set the refresh schedule's time zone explicitly. The default may not be yours, and a refresh that runs at 3am in the wrong time zone runs at an awkward moment in the right one.
  • Schedule refresh after the source is guaranteed updated, not before. If the overnight ETL finishes at 05:30, do not refresh at 05:00 — you will load yesterday's data and everything will look fine.
11

Knowledge Check

Knowledge Check

5 questions. Answer each one to see why the right answer is right.

Your report refreshes perfectly in Desktop but fails in the service. What is the most likely cause?

What does an on-premises data gateway do?

You see Formula.Firewall: Query 'Sales' (step 'Merge') references other queries or steps, so it may not directly access a data source. What is this?

Which source would NOT require a gateway for scheduled refresh?

What is the simplest way to stop a silently-failing refresh going unnoticed for weeks?

12

Practical Challenge

Practical Challenge

Write a pre-publish checklist and a refresh runbook

You are handing a report to a team that will own it after you leave. Produce two documents: a pre-publish checklist you would run before any report goes live, and a runbook the team can follow when refresh fails.

Requirements
  • A pre-publish checklist of no more than eight items
  • For each item, state what to check and where
  • A runbook covering at least five distinct failure symptoms
  • For each symptom: the likely cause, the first diagnostic step, and the fix
  • One item in the runbook must cover what to do when the error message is unhelpful
  • State who should be notified and when

For the checklist, think about the ordered sequence from section 04 — schedule, credentials, gateway, source, transformations, model. Each stage has a pre-flight check.

For the unhelpful-error item, the answer is about where to find better information than the summary message gives you.

13

Solution

Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.

Pre-publish checklist

CheckWhere
1No data source path begins with a drive letter or is a mapped driveHome › Transform data › Data source settings
2File paths are parameterised, or point at SharePoint/OneDriveManage Parameters, and Data source settings
3Privacy levels are set deliberately on every source, not left at Not SetData source settings › Edit Permissions
4Query folding confirmed on any database sourceRight-click each step › View Native Query
5Row counts validated against source for every tableTable view status bar, versus the source
6A 'data as at' timestamp measure exists and is on every pageThe report canvas
7Credentials configured in the service, and one refresh has succeededSemantic model › Settings › Data source credentials, then Refresh history
8Refresh schedule set with an explicit time zone, timed after the source ETL completes, with failure notifications on to at least two peopleSemantic model › Settings › Scheduled refresh
Item 7 is the one people skip
Publishing feels like the finish line, so the credential step gets left for later and then forgotten. Making "watch one refresh succeed" an explicit checklist item — rather than assuming it will — is what turns this from a list into a control.

Refresh failure runbook

SymptomLikely causeFirst stepFix
"Credentials are required to connect"Credentials never set, or expiredSemantic model › Settings › Data source credentialsSign in for each listed source. If a password expired, update it and note the expiry date so it can be renewed before it lapses next time
"Unable to connect. Please check the gateway"Gateway offline, or the source is not registered on itSettings › Manage gateways — is it green, and is this exact server/database listed?Restart the gateway service; add the source to the gateway if missing. If the gateway machine has been rebuilt, the gateway must be re-registered
Formula.FirewallPrivacy level conflictData source settings › Edit Permissions on each sourceSet compatible levels (usually Organizational for internal sources). If genuinely combining trusted internal sources, the ignore-privacy-levels setting is defensible — but it must be set in the service as well as Desktop
"The column 'X' of the table wasn't found"Source schema changedOpen the query in Desktop and find the step naming that columnUpdate the query to the new name. Then contact the source owner — a silent schema change means there is no change-notification process, and that is the real problem
Refresh times outFolding broken, or the source is genuinely slowView Native Query on each step to find where folding stopsReorder steps so folding-breaking operations come last. If it still times out, raise the command timeout and talk to the DBA about indexing or a view
Refresh succeeds but numbers are staleRefresh ran before the source ETL completedCompare the refresh time against the source's own load completion timeMove the schedule later. Better: trigger refresh from the ETL on completion via the API, rather than guessing a time

When the error message is unhelpful

Where to find better information
The message shown on the semantic model's settings page is a summary and is often too generic to act on. Three places give you more:

1. Refresh history. Semantic model › Refresh history › click the failed run. This shows the full error, including the specific query and step that failed — far more than the summary line.

2. Reproduce in Desktop. Open the .pbix and refresh locally. Desktop's error dialogs are considerably more informative, and you can step through Applied Steps to isolate the failing one.

3. The gateway logs. If a gateway is involved, its local log files record the actual source-side error, which the service often reports only as a generic connection failure.

Notification

  • Immediately, automatically: refresh failure notifications to at least two named owners. One person is a single point of failure.
  • Within the hour: if the report is used for a daily operational decision, tell the consumers it is stale before they act on it. A short message beats them discovering it themselves.
  • Same day: if the cause was an unannounced source change, tell the source owner. The fix is not the query edit — it is establishing that they tell you next time.

What makes this a good runbook

Every entry names a specific place to look rather than a principle. Someone unfamiliar with the report can follow it under pressure, which is when runbooks are actually used.

It also distinguishes the fix from the root cause in two places — the schema change and the stale-data case. Editing the query resolves today's failure; establishing a change-notification process with the source owner is what stops it recurring. A runbook that only ever treats symptoms guarantees you will keep reading it.

14

Lesson Summary

Key takeaways
  • Desktop refreshes directly with your local credentials; the service needs credentials configured on the semantic model and, for on-premises sources, a gateway.
  • A gateway runs inside your network and makes an outbound connection — no inbound firewall rule needed. Use a standard gateway, never a personal one, for shared reports.
  • Privacy levels prevent data leaking between sources. Conflicts produce the Formula.Firewall error.
  • Resolve firewall errors by setting compatible privacy levels or restructuring into staging queries — disabling the check should be a deliberate decision.
  • Refresh failures are silent. Enable notifications, to more than one person.
  • Put a 'data as at' timestamp on every page. It makes staleness visible to every reader at zero cost.
  • Configure credentials immediately after publishing and watch one refresh succeed before sharing the link.
  • Schedule refresh after the source ETL completes, with an explicit time zone.

Syllabus topics delivered

Multiple data sourcesData refreshConnection problemsGatewaysCredential and privacy-level errors
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 21% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.