Data Refresh & Fixing Connection Problems
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.
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.
- 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
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.
Concept
Refresh in two places
| Power BI Desktop | Power BI service | |
|---|---|---|
| Trigger | You click Refresh | Scheduled, or triggered manually, or via API |
| Credentials | Stored per user, per machine | Stored in the semantic model's settings |
| On-premises sources | Direct connection | Requires a data gateway |
| Cloud sources | Direct connection | Usually direct, no gateway needed |
| Frequency limits | None — refresh as often as you like | Gated by licence tier |
| Failure visibility | An error dialog you cannot miss | Silent unless notifications are on |
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.
| Mode | What it is | Use for |
|---|---|---|
| Standard | A shared gateway, administered centrally, usable by many users and several services (Power BI, Power Apps, Power Automate) | Anything production. This is what you want |
| Personal | Runs under one user's account, Power BI only, and only works while that machine is on | Personal experiments only. Never for shared reporting |
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.
| Level | Meaning |
|---|---|
| Private | Must never be shared with any other source. Most restrictive |
| Organizational | May be shared with other Organizational sources, but not Public ones |
| Public | May 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
- Set compatible privacy levels. File › Options › Current File › Privacy, and Data source settings › Edit Permissions for each source. Two Organizational sources combine happily.
- 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.
- Ignore privacy levels. Options › Current File › Privacy › Always ignore Privacy Level settings. It works, and it also disables the protection.
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.
Visual Explanation
How a scheduled refresh actually flows, and where it breaks.
The diagnostic table
| Symptom | Most likely cause | First thing to check |
|---|---|---|
| "Credentials are required" after publishing | Credentials were never configured in the service | Semantic model → Settings → Data source credentials |
| "Unable to connect… gateway" | No gateway, or the source is not registered on it | Whether the gateway is online, and whether that exact server/database is in its source list |
Formula.Firewall | Privacy level conflict between sources | Data source settings → Edit Permissions on each source |
| "The column 'X' of the table wasn't found" | The source schema changed — a column renamed or removed | Open the query and find the step referencing it |
| Timeout on a large table | Folding broken, so everything is being pulled locally | View Native Query on each step (Lesson 3) |
| Refresh works in Desktop, fails in the service | A local file path, a mapped drive, or missing gateway configuration | Data source settings — look for a path only you can reach |
Power BI Demonstration
C:\, Z:\) will not refresh in the service.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.
Step-by-Step Exercise
bwb-retail-sales.csv and bwb-products.csv from a local folder.Formula.Firewall message.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.Expected Result
| Step | Result |
|---|---|
| Firewall error | Formula.Firewall: Query 'X' (step 'Y') references other queries or steps, so it may not directly access a data source |
| Both sources set to Organizational | Error resolved, protection retained |
| Restructured into staging + transform | Error resolved, and the query is arguably clearer |
| Ignore privacy levels | Error resolved, protection disabled for the whole file |
| Renamed column | The column 'NetSales' of the table wasn't found, naming the step that references it |
| Adding a tolerant rename step | Refresh succeeds with either column name |
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.
Common Mistakes
Fix: Configure credentials on the semantic model immediately after publishing, then watch one refresh succeed before sharing the link.
Fix: Standard gateway on an always-on server for anything anyone else depends on.
Fix: Try setting compatible privacy levels first. Reserve the override for models combining sources you have already assessed as safe to combine.
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.Fix: Enable notifications on every semantic model that anyone relies on, and send them to more than one person.
Professional Tip
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.
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?
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.
- 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.
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
| Check | Where | |
|---|---|---|
| 1 | No data source path begins with a drive letter or is a mapped drive | Home › Transform data › Data source settings |
| 2 | File paths are parameterised, or point at SharePoint/OneDrive | Manage Parameters, and Data source settings |
| 3 | Privacy levels are set deliberately on every source, not left at Not Set | Data source settings › Edit Permissions |
| 4 | Query folding confirmed on any database source | Right-click each step › View Native Query |
| 5 | Row counts validated against source for every table | Table view status bar, versus the source |
| 6 | A 'data as at' timestamp measure exists and is on every page | The report canvas |
| 7 | Credentials configured in the service, and one refresh has succeeded | Semantic model › Settings › Data source credentials, then Refresh history |
| 8 | Refresh schedule set with an explicit time zone, timed after the source ETL completes, with failure notifications on to at least two people | Semantic model › Settings › Scheduled refresh |
Refresh failure runbook
| Symptom | Likely cause | First step | Fix |
|---|---|---|---|
| "Credentials are required to connect" | Credentials never set, or expired | Semantic model › Settings › Data source credentials | Sign 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 it | Settings › 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.Firewall | Privacy level conflict | Data source settings › Edit Permissions on each source | Set 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 changed | Open the query in Desktop and find the step naming that column | Update 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 out | Folding broken, or the source is genuinely slow | View Native Query on each step to find where folding stops | Reorder 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 stale | Refresh ran before the source ETL completed | Compare the refresh time against the source's own load completion time | Move 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
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.
Lesson Summary
- 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.Firewallerror. - 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
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.