Struggling to refresh API with Oauth, paging

[otravers]

Hi,

Despite spending hours on this I'm still not getting a full solution that handles Oauth bearer tokens + Paging + refreshes that work in the service. I was able to write M code that works in PBI Desktop but doesn't refresh in the service. After tinkering with it, I now have code that in theory may work in the service, but fails to authenticate in PBI Desktop after iterating to the second page during the refresh process.

Does anyone  have sample code they might share that successfully addresses the three requirements above, i.e. refreshing in the service paginated calls to a REST API that uses Oauth for authentication?

Despite googling this extensively, the only solutions I found pointed towards solving the problem outside of Power BI (e.g. Azure Functions), which I'd rather avoid doing if possible.

For the record, I have remixed parts from the following well-known entries, each of them very helpful in their own right:

POST with Oauth2 token, by Steve Howard:

http://angryanalyticsblog.azurewebsites.net/index.php/2016/05/16/api-strategies-with-power-bi/ 

Iterating against cursor-based pagination via a generated list to avoid performance issues from recursive functions. Thanks Gil Raviv!

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/ 

The seminal entry on using RelativePath to work around how the Power BI service (mis)handles API URL evaluation (some people in the comments are facing the same problem that I'm trying to solve here) from @cwebb 

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

Using Json.FromValue to make it easy to pass along parameters in a POST from the always useful @ImkeF 

https://www.thebiccountant.com/2018/06/05/easy-post-requests-with-power-bi-and-power-query-using-jso...

Also, I don't see "Skip Test Connection" in the service, was it ever reintroduced for cloud sources?

https://powerbi.microsoft.com/en-us/blog/skip-test-connection-for-on-premises-and-cloud-data-sources...

You can't even vote anymore on native Oauth support, as if developing custom connectors for thousands of APIs out there was a viable alternative. This is way harder than it should be!

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13195278-oauth

[v-eachen-msft]

Hi @otravers ,

There is a case which is similar to yours, you could refer to it.

https://community.powerbi.com/t5/Service/workaround-for-SCHEDULED-REFRESH-on-HTTP-API-with-pages/m-p...

And you could post your problem in the Power Query forum for better support.

https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

[otravers]

@v-eachen-msft thanks, this helped, I thought using RelativePath was enough but in this instance apparently not. I then got the dreaded "Please rebuild this data combination" error, which I solved by moving the code to get the Oauth token into the main query. And finally this refreshes in the service! I'll have to write a blog post about this because I see many people struggling with similar scenarios.

[JMacC]

You sir are a legend!

Had the same issue, privacy settings did nothing, security settings did nothing... mix of "References other step / query" and "function.firewall" errors...

Moved the function which generates the API key inside the queries which use the API, and it works now!

bit of duplicated code, but refreshes in service so I'm happy

Example instance: paginated API query to MS Graph, fetching all users

let
    makeToken = (#"Azure Graph API Url" as any, #"Azure Tenant ID" as any, #"Azure Application ID" as any, #"Azure Application Client Secret" as any) => let
        loginURL = "https://login.microsoftonline.com/",
        TokenUri = #"Azure Tenant ID" & "/oauth2/token", // which domain is this a token for
        ResourceId = #"Azure Graph API Url", // where is this token for
        TokenResponse = Json.Document(
            Web.Contents(
                loginURL, [
                    RelativePath = TokenUri, 
                    Content = Text.ToBinary(
                        Uri.BuildQueryString([
                            client_id = #"Azure Application ID", 
                            resource = ResourceId, 
                            grant_type = "client_credentials", 
                            client_secret = #"Azure Application Client Secret"
                            ])
                    )
                    , Headers = [Accept = "application/json"], ManualStatusHandling = {400}
                ]
            ) // end web contents
        ), // end json
        AzureAccessTokenB = TokenResponse[access_token] // assign token value
    in
    AzureAccessTokenB,
    GetPages = (Path)=>
        let
            Host = #"Azure Graph API Url",
            Source = Json.Document(
                Web.Contents(
                    #"Azure Graph API Url"
                    , [RelativePath = Path, Headers = [Authorization = "Bearer " & makeToken(
                        #"Azure Graph API Url"
                        , #"Azure Tenant ID"
                        , #"Azure Application ID"
                        , #"Azure Application Client Secret"
                    )]]
                )
            ),
            LL= @Source[value],
            Next = Text.Replace(Source[#"@odata.nextLink"], Host, ""),
            result = try @LL & @GetPages(Next) otherwise @LL
        in
            result,
        Fullset = GetPages("beta/users?$")
    in
        Fullset