Power BI Refresh Works in Desktop but Fails in Service

When Power BI refresh succeeds in Desktop but fails in the service, first compare a current full Desktop refresh with an on-demand service refresh of the same model and source batch. Then follow the failing branch: data transformation, connection, or scheduled-run conditions. A successful preview or yesterday's Desktop refresh is not an equivalent comparison.

This guide covers Import semantic models. It includes a decision table, two fictional incident walkthroughs, and a copyable Power Query diagnostic. The examples illustrate the investigation; your refresh history and source evidence must establish your own cause.

Choose the right troubleshooting branch

Identify the semantic model behind the report. Write its dependency chain: source → dataflow, if any → semantic model → report. Record which operation failed. Import refresh, dataflow refresh, and refreshing visuals are distinct; DirectQuery investigations also depend on source queries during report use. See Microsoft's refresh modes.

Capture the failed run's full error, start time and time zone, table or partition if named, and activity/request identifiers when available. Open the model's Refresh history; compare a successful run and the failed run. Keep identifiers and access-controlled logs in the incident record.

On narrow screens, scroll the tables sideways to see every column.

Use a diagnostic copy in a separate test workspace when isolating queries. Keep the original model, input version, and error details available for comparison. Changing several settings or repeatedly republishing makes the cause harder to identify.

Compare inputs and connection identities

For every source, record the connector, server/database or file path/URL, parameters, source batch identifier, and intended authentication identity. Compare Desktop's source steps with the published model's parameters and gateway/cloud connection settings. Include lookup tables and sources used inside merged queries.

Distinguish the Desktop user's login, stored source credentials, and gateway Windows service account. Confirm which identity the connector uses. An interactive database query under your own login does not establish that the service's source identity has access.

For a gateway-backed source, ask its administrator to test the required route from the gateway host with the intended source authentication and a small read-only query. Check name resolution, resource access, and connector prerequisites. A ports test checks required gateway service connectivity; it is not a complete source-permission test. Microsoft explains these checks in gateway troubleshooting.

Record the model version and source watermark alongside each result. A refresh completion time alone does not tell you which upstream batch was loaded.

Use the error to narrow the next action

A generic IDataReader or external-component exception does not identify one universal cause. Capture its nested error. Microsoft distinguishes step-level and cell-level errors; use the failing step or cell to narrow the investigation. The refresh scenarios reference covers further authentication and processing cases.

Most dynamic sources cannot refresh in the service, with documented exceptions such as supported Web.Contents patterns. Follow the dynamic-source rules for the actual query. Installing a gateway does not by itself make an unsupported query refreshable.

Worked example: Desktop used an earlier batch

In this fictional incident, an orders dataflow exposes units as text. The model converts it to a whole number. Its query and parameters are the same in Desktop and the service, but the first two refreshes read different batches.

The source owner confirms that order 1003 should contain six units and corrects the source. The next upstream batch has five orders and 30 units. Both environments must load that corrected batch successfully. Replacing pending with zero or removing its row would leave 24 units and conceal an incomplete total.

Expose bad rows with Power Query

In a blank query in a diagnostic copy, open the Advanced Editor and paste this self-contained M example. It duplicates the raw text before conversion, selects rows whose typed units value has an error, and returns readable evidence.

let
    Source = #table(
        type table [order_id = number, units_raw = text],
        {
            {1001, "12"},
            {1002, "8"},
            {1003, "pending"},
            {1004, "0"},
            {1005, "4"}
        }
    ),
    WithTypedCopy = Table.DuplicateColumn(
        Source, "units_raw", "units"
    ),
    Converted = Table.TransformColumnTypes(
        WithTypedCopy, {{"units", Int64.Type}}, "en-US"
    ),
    ErrorRows = Table.SelectRowsWithErrors(
        Converted, {"units"}
    ),
    Evidence = Table.RemoveColumns(ErrorRows, {"units"})
in
    Evidence

The expected output is one row: order_id = 1003, units_raw = pending. The original text survives because Table.DuplicateColumn creates a separate conversion target. The conversion uses an explicit culture; see Table.TransformColumnTypes.

Table.SelectRowsWithErrors checks only the named columns when a column list is supplied. Inspect additional typed columns when your actual error points elsewhere. To inspect the conversion error itself in this fixture, temporarily change the final expression from Evidence to Converted and select the error cell.

Reproduce the three input states: remove the fictional order 1003 to represent batch A, restore it with pending for batch B, then change it to "6" for the corrected batch. Expect zero, one, then zero error rows. The final expression Converted shows four rows totaling 24 in batch A and five totaling 30 after correction.

This query is an exception report. Keep it separate from the reporting fact table. For real data, duplicate the query in a test model, preserve the original field before its failing conversion step, and retain the source key and batch identifier. Validate whole-number business rules separately if fractional quantities are possible; successful conversion alone does not prove that rounding was acceptable.

Worked example: a gateway mapping mismatch

In a second fictional incident, the current Desktop refresh succeeds, but the published model cannot map its SQL Server source to the intended gateway connection. The administrator finds this inventory:

These fictional names resolve to the same server in the scenario, but that does not establish a matching Power BI source definition. Microsoft requires matching server and database names and a matching gateway entry for each on-premises source. The model owner must also be allowed to use the connection. See gateway mapping troubleshooting.

The database owner confirms the approved fully qualified name matches the server certificate. The author aligns the diagnostic model's source with that name, and the gateway administrator confirms the intended mapping and source identity. A service refresh then loads the expected batch and totals. The team applies the reviewed change to the shared model and verifies its next scheduled run. The evidence identifies a mapping mismatch; it does not justify installing another gateway or disabling certificate checks.

Investigate scheduled-only failures

If an on-demand service run succeeds, compare its timestamp with the failing schedule. Check whether the upstream batch was ready, whether the gateway host restarted, and whether the source was reachable under the mapped identity at that time. Correlate those observations with gateway and source-owner logs rather than assuming a later success cleared the cause.

For a cluster, record the member involved and compare member versions and prerequisites. Microsoft notes that inconsistent member versions can cause refresh failures in its gateway guidance. Coordinate maintenance with the owner because the gateway may serve other models.

Check the configured schedule and time zone, its enabled state, and failure notifications. A disabled schedule needs restoration after the underlying problem is corrected. Microsoft's scheduled refresh configuration explains those controls. Confirm the next actual scheduled execution; an on-demand run exercises a different time window.

Verify recovery and record the cause

After the reviewed correction, require an on-demand service success and the next scheduled success. Check refresh history, loaded source watermark, expected row count, exception count, and a control total. Refresh the report visuals and inspect representative filters. Use the metric reconciliation guide if the displayed result still differs.

Leave a short incident record with the owner: failed run identifiers, input/model versions, connection mapping, confirmed cause, correction, and recovery evidence. Record who owns the source and gateway, keeping credentials out of notes and screenshots. The recurring report workflow helps turn those checks into a repeatable handoff.

FAQ

Why does Keep Errors return no rows when refresh still fails?

You may be inspecting the wrong column, query, or input batch. A step-level connection or missing-column error can prevent a table from being returned at all; an error-row filter cannot diagnose a table it never receives. Start with the first failing step and full error details.

Should I delete the Changed Type step to make refresh work?

First establish why the conversion fails and what type the model requires. Removing the step may move the failure downstream or change calculations. Preserve raw values, review exceptions with the source owner, and test the intended type before changing the shared model.

Do cloud sources always work without a gateway?

No. The actual network route and connector requirements determine that. A private endpoint or required connector software can introduce a gateway dependency; Microsoft's gateway planning guidance covers those cases.