Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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
@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.
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.