Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
otravers
Community Champion
Community Champion

Struggling to refresh API with Oauth, paging

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

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals
3 REPLIES 3
v-eachen-msft
Community Support
Community Support

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

@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.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors