Forum Discussion

Joris_NL's avatar
Joris_NL
Helper III
2 years ago

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

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

Need Help

6 Replies

  • blopez11's avatar
    blopez11
    Super User
    2 years ago

    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.

     

  • Joris_NL's avatar
    Joris_NL
    Helper III
    2 years ago

    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's avatar
      blopez11
      Super User
      2 years ago

      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-in-power-query-and-power-bi-m-code/

       

      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's avatar
        Joris_NL
        Helper III
        2 years ago

        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
    ),