Forum Discussion
'Dynamic data source' error for Planned Refresh on semantic model
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"
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.
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 ),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.
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!