powerquery – Unable to refresh dynamic data source in PowerBI service via using anonymous web API access?

SaveSavedRemoved 0
Deal Score0
Deal Score0


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-
https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/

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 !!

Thanks!



Source link

We will be happy to hear your thoughts

Leave a reply

Logo
Enable registration in settings - general