'Dynamic data source' error for Planned Refresh on semantic model

[Joris_NL]

Hi,

I'm new to publishing and would like to make a scheduled refresh. But I get the following error:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
Data source for Query1

I'm using Imported data. Most data is from an api source. And yes I also import one Excel-table. But I changed the source to a sharepoint url, so  =Excel.Workbook(Web.Contents("https://companyname.sharepoint.com/sites/MyTeam/Shared%20Documents/%20caseload.xlsx"), null, true.

I refreshed all sourced and app pages.

Now I can push 'refresh' manually in the app, and it does seem to refresh. But trying a scheduled refresh gives the above error. The option for a gateway is also simply missing.

Please help!

Joris

[Joris_NL]

Thanks. Here's the API.

I didn't make the 10000 requests part myself. But it was necessary because the API only allowed 100 records at a time.

Is this a problem for a planned refresh?

let
    BaseURL = "https://api.onview.nl/ETCETCETC",
    PageSize = 100,
    MaxLimit = 10000,
    PagesToFetch = List.Generate(
        ()=> [PageNumber=1, Offset=0],
        each [Offset] < MaxLimit,
        each [PageNumber=[PageNumber]+1, Offset=[Offset]+PageSize]
      
    ),
    AllData = List.Transform(PagesToFetch, (page) => 
        let
            Response = Json.Document(
                Web.Contents(
                    BaseURL & 
                    "?offset=" & Text.From(page[Offset]) & 
                    "&limit=" & Text.From(PageSize),
                    [Headers=[#"Content-Type"="application/json"]]
                )
            )
        in
            Response
    ),
    CombinedData = Table.FromList(AllData, Splitter.SplitByNothing()),
    #"Expanded Column1" = Table.ExpandRecordColumn(CombinedData, "Column1", {"Result", "TotalItems", "NextPage", "Content"}, {"Column1.Result", "Column1.TotalItems", "Column1.NextPage", "Column1.Content"}),
    #"Removed Result/Total/NextPage" = Table.RemoveColumns(#"Expanded Column1",{"Column1.Result", "Column1.TotalItems", "Column1.NextPage"}),
    #"Expanded Column1.Content" = Table.ExpandListColumn(#"Removed Result/Total/NextPage", "Column1.Content"),
    #"Expanded Column1.Content1" = Table.ExpandRecordColumn(#"Expanded Column1.Content", "Column1.Content", {"RecId", "DossierId", "WorkflowId", "WorkflowTitle"}, {"WorkflowRegelnr", "DossierRecId", "Workflowtype.RecId", "Workflow.Omschrijving"}),
    #"Filtered DossierRecId <> Test" = Table.SelectRows(#"Expanded Column1.Content1", each ([DossierRecId] <> 374)),
    #"LET OP: hierna negeer ik regelnr" = #"Filtered DossierRecId <> Test",
    #"Removed Duplicates behalve regnr" = Table.Distinct(#"LET OP: hierna negeer ik regelnr", {"DossierRecId",  "Workflowtype.RecId", "Workflow.Omschrijving"}),
    #"Added Workflowsleutel" = Table.AddColumn(#"Removed Duplicates behalve regnr", "WorkflowSleutel", each ([DossierRecId] * 1000)  + [Workflowtype.RecId]),
    #"Merged CaseloadZwaarte" = Table.NestedJoin(#"Added Workflowsleutel", {"Workflowtype.RecId"}, CaseloadZwaarte, {"WF type nr"}, "CaseloadZwaarte", JoinKind.LeftOuter),
    #"Expanded CaseloadZwaarte" = Table.ExpandTableColumn(#"Merged CaseloadZwaarte", "CaseloadZwaarte", {"Caseload punten"}, {"Caseload punten"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded CaseloadZwaarte",{{"WorkflowRegelnr", Int64.Type}, {"DossierRecId", Int64.Type}, {"Workflowtype.RecId", Int64.Type}, {"Workflow.Omschrijving", type text}, {"WorkflowSleutel", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"WorkflowRegelnr"}),
    #"Filtered eventuele fouten" = Table.SelectRows(#"Removed Duplicates", each [WorkflowRegelnr] <> null and [WorkflowRegelnr] <> ""),
    #"Appended Query" = Table.Combine({#"Filtered eventuele fouten", #"Acties (Task) zonder workflow"})
in
    #"Appended Query"

[blopez11]

Yes, that looks like the culprit.  The first parameter to web.contents should be static, only "https://api.onview.nl".  Then use the RelativePath and Query options in web.contents to build out the remainder of the URL.  You can take a look at the following for RelativePath and Query options: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

However, I have found working with REST APIs in PowerQuery is like a puzzle, as there may be other issues you run into (i.e. authentication) that may not be easily solved.  What works in the Desktop does not always work in the Service.

Good luck.

[Joris_NL]

Thanks.
First I read the blog you linked. Then I asked ChatGPT to cut the URL and use Relativepath. The original URL query only asked for an API key and a table name, nothing more. So solving that seemed easy. However the query (still) works in Desktop, but trying Scheduled refresh gives me the same 'dynamic data source' error as before. 

Cluesless now. If anyone has any more ideas, I'd be happy to try them out.

let
    BaseURL = "https://api.onview.nl/api/",
    APIKey = "10dc841a-...-83d1-6207f1dba36a",
    RelativePath = "/dossier",
    PageSize = 100,
    MaxLimit = 10000,
    PagesToFetch = List.Generate(
        ()=> [PageNumber=1, Offset=0],
        each [Offset] < MaxLimit,
        each [PageNumber=[PageNumber]+1, Offset=[Offset]+PageSize]
      
    ),
    
    // Constructing the URL with relative path
    ConstructedURL = BaseURL & APIKey & RelativePath,

    // Fetching data
    AllData = List.Transform(PagesToFetch, (page) => 
        let
            Response = Json.Document(
                Web.Contents(
                    ConstructedURL & 
                    "?offset=" & Text.From(page[Offset]) & 
                    "&limit=" & Text.From(PageSize),
                    [Headers=[#"Content-Type"="application/json"]]
                )
            )
        in
            Response
    ),

[blopez11]

You are still using a dynamic value for the first parameter of web.contents.  You need to look at using the option properties of web.contents.  Please see https://learn.microsoft.com/en-us/powerquery-m/web-contents and the above referenced article.

[Joris_NL]

I'm trying so hard but I'm getting nowhere.

On the 'web-contents' page of your link, I think example 3 applies. But the example doesn't have a field for the api key value... then how should this work? Makes me question this approach.

I asked ChatGPT to solve the issue but it keeps making the url in web.content a dynamic value instead of the base url.

Despite those 2 problems I tried it myself. Didn't work so far.

    AllData = List.Transform(PagesToFetch, (page) => 
        let
            Response = Json.Document(
                Web.Contents(
                    "https://api.onview.nl", 
                    [ApiKeyName = "/api",
                    ApiKeyValue = "10dc841a-0745-...-6207f1dba36a",
                    RelativePath = "/dossier"],
                    "?offset=" & Text.From(page[Offset]) & 
                    "&limit=" & Text.From(PageSize),
                    [Headers=[#"Content-Type"="application/json"]]
                )
            )

Errors with too many arguments in this function (expecting 1 or 2). I tried different numbers of brackets or removing ApiKeyValue which doesn't even exist as a function(?) but getting nowhere.

Any help would be appreciated!

[blopez11]

It is most likely the api source that is the cause.  Can you post the M code you are using, primarily how you are constructing the url?  That would help in debugging.