Fix "you cannot schedule refresh" on a Power BI dynamic data source

The report refreshes happily in Power BI Desktop. You publish it, open its settings in the service, and the service won't schedule a refresh. When the query is fine in Desktop, the cause is almost always a dynamic data source: the query works out its own web address, file path or server name while it runs, so Power BI can't tell before the refresh what it will connect to. This guide finds that query and rewrites it so the service can refresh it.

What makes a data source dynamic

Before a refresh starts, the service has to know every source a semantic model reads from, so it can attach your credentials to each one and route on-premises sources through a gateway. Microsoft defines a dynamic data source as one where some or all of the connection details can't be known until Power Query runs the query, because they're generated in code or returned from another source. Its examples are the instance name and database of a SQL Server database, the path of a CSV file, and the URL of a web service.

In most cases, Microsoft says, a semantic model with a dynamic data source can't be refreshed in the Power BI service. It names the exceptions that matter for a fix:

• Web.Contents calls that use the RelativePath and Query options;
• queries that reference Power Query parameters.

And one that doesn't work: switching between data sources with dynamic M query parameters isn't supported in the service.

Find the query that builds its address

1. In Power BI Desktop, select Transform data on the ribbon to open Power Query Editor.
2. Open Data source settings and select Data sources in current file.
3. Look for a warning in that window. Microsoft's documentation shows it as a note that some data sources may not be listed because of hand-authored queries. If it's there, the file contains a dynamic data source that the service can't refresh.
4. Open each query's Advanced Editor and look at the first argument of every source function (Web.Contents, Sql.Database, File.Contents, Csv.Document over a computed path, and so on). A text value written out in full is static. Anything joined with &, built with Text.Combine, or taken from a column or another query is dynamic.

If nothing is dynamic and the service still refuses, the source itself may not support refresh in the service. Microsoft's fix for that case: note the provider named at the top of the query in the Advanced Editor and compare it with its list of Power BI data sources.

Fix a web API with RelativePath and Query

This is the most common case: an API that's called once per page, per date or per ID. The broken version builds the whole URL as text:

// Won't refresh in the service: the URL exists only while the query runs
let
    GetPage = (page as number) =>
        Json.Document(
            Web.Contents("https://api.lowcodestacks.example/v1/orders?page=" & Number.ToText(page))
        ),
    Pages = List.Transform({1..5}, GetPage)
in
    Pages

The version that refreshes keeps the first argument of Web.Contents a fixed base URL. Everything that changes goes into the options record: RelativePath is added to the end of the base URL, and Query adds the query string for you, escaping the values. Pass the values as text, as Microsoft's own example does.

// Refreshes: the base URL is fixed, only the path and the query string change
let
    GetPage = (page as number) =>
        Json.Document(
            Web.Contents(
                "https://api.lowcodestacks.example",
                [
                    RelativePath = "v1/orders",
                    Query = [page = Number.ToText(page)]
                ]
            )
        ),
    Pages = List.Transform({1..5}, GetPage)
in
    Pages

The service now sees one source, https://api.lowcodestacks.example, and you sign in to it once. If the API takes a key in the query string, don't paste the key into the URL: name it with the ApiKeyName option and enter the value as a Web API credential, so it's stored with the credential and not in your M code.

Fix a server, database or path with a parameter

When the part that changes is a server, database, folder or site, which usually differs only between test and production, turn it into a Power Query parameter instead of reading it from a table or working it out in code.

1. In Power Query Editor, select Manage Parameters > New Parameter.
2. Give it a name such as SqlServer, the type Text, and the current server name as its Current Value, then select OK.
3. In the query, replace the computed value with the parameter: Sql.Database(SqlServer, "Sales").

After you publish, change the value in the service without editing the file: in the service's Settings, on the Semantic models tab, select the model and expand Parameters. Microsoft's refresh documentation uses the same pattern, with schema and table names held in parameters.

For files, point the Folder or SharePoint folder connector at a fixed root folder or site, and filter the list of files in Power Query. The source is then that one fixed location, however many files you pick from it.

Republish and set up the refresh

1. Refresh in Desktop and check the numbers haven't changed, then publish.
2. In the service, open the semantic model's settings. Under Data source credentials, select Edit credentials for each source and sign in. An on-premises source needs a gateway connection instead.
3. Turn the refresh schedule on, then run Refresh now once and check Refresh history to confirm it succeeded.

If the refresh now starts but fails, that's a different problem: our refresh failures checklist covers credentials, gateways and timeouts, and the key didn't match any rows covers a table that moved at the source.

Sources

• Data refresh in Power BI: refresh and dynamic data sources — Microsoft Learn
• Troubleshooting unsupported data source for refresh — Microsoft Learn
• Web.Contents: the RelativePath, Query and ApiKeyName options — Microsoft Learn
• Using parameters in Power Query — Microsoft Learn
• Edit parameter settings in the Power BI service — Microsoft Learn
• Dynamic datasource error with RelativePath — Fabric community forum thread (a lead, confirmed against the Learn pages above)