powerquery – Unable to refresh dynamic data source in PowerBI service via using anonymous web API access?
This relates to my earlier question – How to iterate/loop through next pages in an API request in PowerQuery/PowerBI? ; which was resolved using below code:
//Declare base variables let BaseURL = "https://api.aaaaaa.com", Entity = "/api/v1/user?&limit=1000", Token = "zzzzzzzzzzzzzzzzzzzzzzzzzzzz", Options = [Headers=[APITOKEN=Token]], URL = BaseURL & Entity, //Define a function that would take step/page as parameter and return results GetData=(page as number) => let Source = Json.Document(Web.Contents(URL & "&step=" & Number.ToText(page), Options)), Data = try Source[results] otherwise null in Data, //Iterate over GetData () to return all the records until last page i.e. until no "result" is retrieved from the API call GeneratePageList = List.Generate( ()=> [Result = try GetData(1) otherwise null, Page=1], each [Result] <> null, each [Result = try GetData([Page]+1) otherwise null, Page=[Page]+1], each [Result] ) in GeneratePageList
However, once this code is published to PowerBI service, we cannot schedule refresh for it, since it gives below error as:-
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 Query1Discover Data Sources
Tried RelativePath & Query method as suggested here – 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/ and here-
But, without any luck, see below how am using it:
let BaseURL = "https://api.crewhu.com", Entity = "/api/v1/user?&limit=1000&step=", Token = "60afbdaf5d7d584762771f36", Options = [Headers=[X_CREWHU_APITOKEN=Token]], URL = BaseURL & Entity, //Define a function that would take step/page as parameter and return results GetData=(page as number) => let Source = Json.Document(Web.Contents(BaseURL & [RelativePath = Entity, Query=[page]], Options)),
The BaseURL is reachable; but redirects to the login page, where our admin credentials (username+password) on the vendor site works well. However, same credentials do not work when using “Basic” connection method during accessing Web Content.
We’ve only got an Open API token/key from the vendor; but even that token/key also doesn’t work from when providing that in “Web API” section during connecting/accessing Web Content, it gives error as:- “a web api key can only be specified when a web api key name is provided”, but the same key/token works well from within PowerQuery (M) code using anonymous web api call method.
Have tried multiple permutation combinations of providing key/token in the username/password fields as suggested in some sites, but still no luck.
Please suggest/help !!