- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Using Json.FromValue to make it easy to pass along parameters in a POST from the always useful @ImkeF
Also, I don't see "Skip Test Connection" in the service, was it ever reintroduced for cloud 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @otravers ,
There is a case which is similar to yours, you could refer to it.
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
If this post helps, then please consider Accept it as the solution to help the other members find it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Subject | Author | Posted | |
---|---|---|---|
02-21-2025 11:25 AM | |||
10-17-2024 05:20 AM | |||
08-02-2024 03:58 AM | |||
11-11-2024 12:10 AM | |||
12-19-2024 05:18 AM |