Why Your Power BI Refresh Works in Desktop but Fails in the Service

Scenario: The Sales Analytics Dataset That Only Fails in the Cloud

You own a Sales Analytics dataset:

• Import model
• Multiple sources:
  • On-prem SQL Server (production)
  • Excel files on a network share
  • A couple of web APIs
• Published to a workspace in a shared capacity

Symptoms:

• Desktop: Refresh succeeds, visuals look good.
• Service (manual or scheduled): Refresh fails with one of:
  • The credentials provided for the source are invalid.
  • We couldn't refresh the dataset. Please try again later.
  • Data source error: Unable to connect to the data source.

Nothing obvious is wrong. The SQL server is up, the files are there, your own account has access. The root issue: Desktop and Service are different execution environments.

We’ll stay with this Sales Analytics dataset and peel back the main causes:

1. How Desktop vs Service actually connect
2. Gateway and network path differences
3. Authentication modes that work locally but not in the Service
4. Query features that behave differently in the Service
5. Capacity, timeouts and refresh workload

1. Desktop vs Service: Two Different Runtimes

Power BI Desktop and the Power BI service do not share a runtime or identity.

What Desktop does

Power BI Desktop refresh:

• Runs on your local machine.
• Uses your interactive sign-in and local network access.
• Can see:
  • Your VPN sessions
  • Your mapped drives (e.g. Z:\SalesFiles) if the engine supports them
  • Enterprise resources reachable from your device

What the Service does

Power BI Service refresh:

• Runs in Azure, in the tenant’s region.
• Uses service-side identities:
  • For cloud sources: user identities (organizational/Azure AD) or service principals, depending on how credentials are configured.
  • For on-prem sources: the gateway’s data source credentials.
• Has no direct access to:
  • Your laptop
  • Your VPN-only network segments
  • Your mapped drives or local file system

If a dataset depends on anything that only exists in your Desktop context, the refresh will succeed locally and fail in the Service.

For our Sales Analytics dataset, the most common pattern:

• Desktop: connects to Z:\SalesFiles\Monthly.xlsx via your mapped drive.
• Service: cannot see Z: at all; it needs a gateway connection to the underlying UNC path, e.g. \\fileserver\SalesFiles.

2. Gateway and Network Path Issues

For on-premises sources, the Power BI service must go through an on-premises data gateway. Desktop does not.

Typical gateway-related failure modes

1. No configured gateway for the dataset’s sources

   • Symptom: Refresh fails with a credentials or connection error.
   • Dataset settings show: no usable gateway or no matching data source.

2. Gateway exists but doesn’t match the dataset’s connection string

   • Example: Desktop uses Server=SQLPROD01;Database=SalesDW, but the gateway data source is configured as SQLPROD01.corp.local.
   • The service tries to bind the dataset’s source to a gateway data source by server name and database. If they differ, the dataset can’t use that gateway.

3. File share paths that work locally but not via gateway

   • Desktop: \\fileserver\SalesFiles\Monthly.xlsx or Z:\SalesFiles\Monthly.xlsx.
   • Gateway: must be configured with the UNC path and must run on a machine that can access that share.

What to fix in the Sales Analytics scenario

For each on-prem source:

1. Go to Power BI service → Workspace → Dataset → Settings → Gateway and cloud connections.
2. Check if the dataset shows a Gateway connection with a matching gateway.
3. In the On-premises data gateway configuration:
   • Create a data source for each on-prem resource:
     • SQL Server: same server and database as in Power BI Desktop.
     • File share: UNC path (no mapped drive letters).
   • Configure credentials (Windows or SQL auth) that have access.
4. Re-publish the PBIX if you changed connection strings.

Once the gateway and paths match, the Service refresh should stop failing with basic connectivity errors.

3. Authentication: Why Your Credentials Don’t Survive the Trip

Authentication is where many "works in Desktop, fails in Service" issues live.

Desktop behaviour

In Desktop you can use:

• Windows / current user for on-prem SQL and file shares
• Organizational account (Azure AD) for cloud sources
• API keys / Basic auth for some web APIs

Desktop stores credentials in your local Power BI credential store and uses your identity when connecting.

Service behaviour

In the Service, each data source in the dataset is bound to service-managed credentials:

• For on-prem via gateway: credentials configured in the gateway data source.
• For cloud sources: credentials configured in the dataset’s Data source credentials section.

Common mismatches:

1. Desktop uses Windows auth; Service uses different credentials via gateway

   • Gateway data source may be configured with SQL credentials or a different Windows account than your Desktop user.
   • If the server is configured differently or Kerberos is involved, the gateway may fail to connect.

2. OAuth / organizational account expired or not granted consent

   • Cloud sources like SharePoint, OneDrive, or some SaaS APIs use OAuth.
   • Service refresh fails when:
     • The token has expired and cannot be silently renewed.
     • The user who configured the credentials lost access.

3. Web APIs that rely on your local environment

   • Desktop calls https://internal-api.corp.local/sales via your VPN.
   • Service cannot reach corp.local at all; gateway doesn’t help for HTTP endpoints unless configured and reachable from the gateway machine.

Practical steps for the Sales Analytics dataset

For each source in the Service:

1. Go to Dataset → Settings → Data source credentials.
2. Validate:
   • Authentication method (Organizational, Basic, Windows, etc.) matches what the source supports for service-side access.
   • The account used has access and is appropriate for a server-side process (often a service account or service principal).
3. For SharePoint/OneDrive sources:
   • Re-enter credentials and ensure the account has at least read access.
4. For web APIs:
   • Confirm they are reachable from the Service or through a gateway.
   • If they’re internal, consider an on-prem gateway with a custom connector or moving that logic to a service that the gateway can reach.

If you’re using parameters for server names or URLs, check that the parameter values in Desktop match those in the Service after publish.

4. Query Features That Behave Differently in the Service

Power Query in Desktop and in the Service use the same language but can differ in runtime capabilities and privacy behaviour.

Data privacy levels and combining sources

Data privacy levels (Public, Organizational, Private) can change how queries are folded and combined.

• Desktop: you may have disabled privacy checks or set broad levels.
• Service: privacy evaluation can be stricter, especially when combining:
  • On-prem sources via gateway
  • Cloud sources

Typical symptom:

• Desktop refresh: succeeds, maybe with a warning.
• Service refresh: fails with a data source privacy or "unable to combine" error.

To reduce problems:

1. In Desktop, set privacy levels deliberately for each source.
2. Avoid unnecessary source combinations in a single query when they cross boundaries (on-prem + cloud).
3. Consider staging tables per source and combining them after load in the model instead of in Power Query when privacy rules get in the way.

Unsupported or constrained features in Service

Some behaviour differences that matter in practice:

1. Local file references

   • Any query that references a local path like C:\Users\... or a removable drive will never work in the Service.
   • Use SharePoint, OneDrive, or a file share via gateway instead.

2. R and Python scripts

   • Desktop can run R/Python scripts during refresh.
   • In the Service, scheduled refresh of datasets that use R or Python in Power Query is only supported in specific scenarios and requires configuration; many R/Python-based data sources are not supported for scheduled refresh.
   • If your Sales Analytics dataset uses R/Python in Power Query, expect Service refresh to fail unless you confirm current support and configuration for your tenant and gateway.

3. Custom connectors

   • Desktop can use custom connectors (.mez files) installed locally.
   • Service requires the connector to be available on the gateway and supported for refresh.

Example: Query that works locally but not in Service

Suppose you have a Power Query step that reads a local CSV and merges it with SQL data:

let
    SourceSql = Sql.Database("SQLPROD01", "SalesDW"),
    SalesTable = SourceSql{[Schema="dbo", Name="FactSales"]}[Data],
    LocalCsv = Csv.Document(
        File.Contents("C:\\Data\\Targets.csv"),
        [Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.Csv]
    ),
    Merged = Table.NestedJoin(
        SalesTable,
        {"SalespersonId"},
        LocalCsv,
        {"SalespersonId"},
        "Targets",
        JoinKind.LeftOuter
    )
in
    Merged

This will never refresh in the Service because File.Contents("C:\...") is only valid on your machine. The fix is to move Targets.csv to a supported location (e.g. SharePoint) and update the query to use that path.

5. Capacity, Timeouts, and Refresh Workload

Sometimes both Desktop and Service can technically connect, but the Service fails due to resource limits.

Differences in refresh limits

• Desktop:

  • Uses your machine’s CPU, memory and network.
  • No enforced service timeout; it runs until it finishes or your machine gives up.

• Service (shared capacity):

  • Enforces refresh timeouts and resource limits.
  • Large models or long-running queries may be cancelled.

Typical symptom in our Sales Analytics dataset:

• Desktop refresh: takes a long time but eventually succeeds.
• Service refresh: fails with a generic "timeout" or "internal error" message.

Practical mitigations

1. Reduce data volume per refresh

   • Implement incremental refresh for fact tables where applicable.
   • Filter out historical data that is rarely used.

2. Improve query folding

   • Ensure Power Query steps fold to the source where possible.
   • Avoid row-by-row operations in M that break folding (e.g. complex custom functions on each row).

3. Check capacity and refresh schedule

   • If multiple heavy datasets refresh at the same time, they compete for resources.
   • Stagger refresh schedules or move heavy datasets to a Premium capacity if available.

4. Monitor refresh diagnostics

   • Use refresh history and any available monitoring to identify which table or source is slow.

These mitigations don’t change basic connectivity rules, but they turn "works locally, fails in Service" due to resource constraints into a stable scheduled refresh.

Putting It All Together for the Sales Analytics Team

Back to the Sales Analytics dataset. A realistic resolution path looks like this:

1. Normalize connection strings

   • Replace Z:\SalesFiles\Monthly.xlsx with \\fileserver\SalesFiles\Monthly.xlsx in Desktop.
   • Re-publish.

2. Configure gateway properly

   • Add data sources for SQLPROD01 and \\fileserver\SalesFiles.
   • Use credentials that have read access and match the auth mode you want in the Service.

3. Fix unsupported local references

   • Move any local CSVs or Excel files to SharePoint or an accessible file share.
   • Update Power Query to use the new locations.

4. Align authentication

   • Set organizational account credentials for SharePoint sources in the Service.
   • Use service accounts or service principals where appropriate.

5. Review refresh load

   • If refresh is still failing under load, apply incremental refresh or adjust schedules.

Once those changes are in place, you should see the pattern flip: refresh is consistent in both Desktop and Service, and failures are now rare and explainable.

One Practical Takeaway

When Power BI refresh works in Desktop but fails in the Service, start by assuming environment mismatch, not query logic. Trace each data source from Desktop to Service: path, gateway, authentication, and runtime support. Fix those four for your main datasets and most "mysterious" refresh failures disappear.