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.
I used this guide to create a custom connector to connect to an API that uses OAuth 2.0: https://jussiroine.com/2019/02/building-a-custom-connector-for-power-bi-that-supports-oauth2-to-visu...
I was able to set everything up successfully, however, every time I want to change the API URL, Power BI treats it as a new source. I had to setup pagination to paginate through the API results as they limit results and each pagination is a different URL string. Power BI treats each of these pages as a new source. So if I have 10 pages of results, Power BI is prompting me with the same login 10 times.
I read about data source paths and required parameters that should make Power BI understand that the base url is truly the main "source" of data thus not prompting me 10 times for credentials.
https://learn.microsoft.com/en-us/power-query/handlingauthentication#data-source-paths
I'm just not sure how to implement this in the code below?
// This file contains your Data Connector logic
section OuraCloudConnector;
// OuraCloud OAuth2 values
client_id = Text.FromBinary(Extension.Contents("client_id.txt"));
client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";
token_uri = "https://api.ouraring.com/oauth/token";
authorize_uri = "https://cloud.ouraring.com/oauth/authorize";
logout_uri = "https://login.microsoftonline.com/logout.srf";
// Login modal window dimensions
windowWidth = 720;
windowHeight = 1024;
// OAuth2 scope
scope_prefix = "";
scopes = {
"daily"
};
[DataSource.Kind="OuraCloudConnector", Publish="OuraCloudConnector.Publish"]
shared OuraCloudConnector.Contents = (url as text) =>
let
source = Json.Document(Web.Contents(url))
in
source;
// Data Source Kind description
OuraCloudConnector= [
TestConnection = (dataSourcePath) => { "OuraCloudConnector.Contents", dataSourcePath },
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
],
Label = Extension.LoadString("DataSourceLabel")
];
// Data Source UI publishing description
OuraCloudConnector.Publish = [
Beta = true,
Category = "Other",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = OuraCloudConnector.Icons,
SourceTypeImage = OuraCloudConnector.Icons
];
// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
response_type = "code",
client_id = client_id,
redirect_uri = redirect_uri,
state = state,
scope = GetScopeString(scopes, scope_prefix)
])
in
[
LoginUri = authorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
Context = null
];
FinishLogin = (context, callbackUri, state) =>
let
// parse the full callbackUri, and extract the Query string
parts = Uri.Parts(callbackUri)[Query],
// if the query string contains an "error" field, raise an error
// otherwise call TokenMethod to exchange our code for an access_token
result = if (Record.HasFields(parts, {"error", "error_description"})) then
error Error.Record(parts[error], parts[error_description], parts)
else
TokenMethod("authorization_code", "code", parts[code])
in
result;
Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);
Logout = (token) => logout_uri;
// see "Exchange code for access token: POST /oauth/token" at https://cloud.ouraring.com/docs/authentication for details
TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
grant_type = "authorization_code",
redirect_uri = redirect_uri,
client_id = client_id,
client_secret = client_secret
],
queryWithCode = Record.AddField(queryString, tokenField, code),
tokenResponse = Web.Contents(token_uri, [
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = {400}
]),
body = Json.Document(tokenResponse),
result = if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body
in
result;
Value.IfNull = (a, b) => if a <> null then a else b;
GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
let
prefix = Value.IfNull(scopePrefix, ""),
addPrefix = List.Transform(scopes, each prefix & _),
asText = Text.Combine(addPrefix, " ")
in
asText;
OuraCloudConnector.Icons = [
Icon16 = { Extension.Contents("OuraCloudConnector16.png"), Extension.Contents("OuraCloudConnector20.png"), Extension.Contents("OuraCloudConnector24.png"), Extension.Contents("OuraCloudConnector32.png") },
Icon32 = { Extension.Contents("OuraCloudConnector32.png"), Extension.Contents("OuraCloudConnector40.png"), Extension.Contents("OuraCloudConnector48.png"), Extension.Contents("OuraCloudConnector64.png") }
];
Hi @PowerUser123 ,
Have you considered about using a parameter to combine the source together? Is this feasible?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |