Hi Experts,
I am encountering an issue with a custom Power Query connector that uses OAuth2 authentication in Power BI. The connector is designed to retrieve data from an API, and it works correctly when fetching data from a single endpoint. However, I face a significant problem when iterating over multiple URLs to retrieve data from different projects.
Problem Description:
Every time the connector accesses a new URL, Power BI prompts for re-authentication. This results in multiple authentication requests, which is impractical and disrupts the data retrieval process. Specifically, the authentication token seems to become invalid whenever a different URL is accessed, causing the need for repeated logins.
Observed Behavior:
When accessing different URLs within the same session, Power BI repeatedly prompts for re-authentication. This issue seems to stem from Power BI treating each unique URL as a separate data source, invalidating the current token.
Desired Outcome:
- Implement token caching or reuse the existing token across multiple URLs within the same session until it expires.
- Enable seamless data retrieval without repeated authentication prompts for each URL.
Is it possible to use the same connector with the same credentials (same authentication) across different URLs in Power BI? If yes, how can this be achieved? Or is there a workaround?
Current Connector Code:
// This file contains your Data Connector logic
section buildagil;
// OuraCloud OAuth2 values
client_id = "XXXXX";
client_secret = "XXXXX";
redirect_uri = "http://localhost";
token_uri = "XXXXX";
authorize_uri = "XXXXX";
logout_uri = "XXXXX";
// Login modal window dimensions
windowWidth = 720;
windowHeight = 1024;
// OAuth2 scope
scope_prefix = "";
scopes = {
"openid",
"offline_access",
"bcf"
};
[DataSource.Kind="buildagil", Publish="buildagil.Publish"]
shared buildagil.Contents = Value.ReplaceType(UrlImpl, UrlType);
shared buildagil.GetAccessToken = GetAccessToken; // Expose the access token function
UrlType = type function(
url as (type text meta [
Documentation.Description = "Specify api url",
Documentation.SampleValues = {"https://api.buildagil.com/bcf/3.0/projects"}
]))
as table meta [
Documentation.Name = "buildagil"
];
UrlImpl = (url as text) =>
let
// Get the access token
tokenResponse = GetAccessToken(),
accessToken = tokenResponse[access_token],
// Fetch data from the API with the access token
source = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & accessToken]]))
in
source;
// Function to get the access token
GetAccessToken = () as record =>
let
token = Extension.CurrentCredential()[access_token]
in
token;
// Data Source Kind description
buildagil = [
TestConnection = (dataSourcePath) => { "buildagil.Contents", dataSourcePath },
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
],
Label = Extension.LoadString("DataSourceLabel")
];
// Data Source UI publishing description
buildagil.Publish = [
Beta = false,
Category = "Other",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = buildagil.Icons,
SourceTypeImage = buildagil.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 = windowHeight,
WindowWidth = windowWidth,
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;
TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
grant_type = grantType,
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;
buildagil.Icons = [
Icon16 = { Extension.Contents("buildagilCloudConnector16.png"), Extension.Contents("buildagilCloudConnector20.png"), Extension.Contents("buildagilCloudConnector24.png"), Extension.Contents("buildagilCloudConnector32.png") },
Icon32 = { Extension.Contents("buildagilCloudConnector32.png"), Extension.Contents("buildagilCloudConnector40.png"), Extension.Contents("buildagilCloudConnector48.png"), Extension.Contents("buildagilCloudConnector64.png") }
];
Current Query Code:
let
// Function to query project data
GetProjects = () =>
let
Source = buildagil.Contents("https://api.buildagil.com/bcf/3.0/projects"),
ProjectsTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedProjects = Table.ExpandRecordColumn(ProjectsTable, "Column1", {"project_id", "name"}, {"project_id", "name"})
in
ExpandedProjects,
// Function to query topics for a given project
GetTopics = (project_id as text) =>
let
url = Text.Format("https://api.buildagil.com/bcf/3.0/projects/#{0}/topics?$top=100000", {project_id}),
TopicsResponse = buildagil.Contents(url),
TopicsTable = Table.FromList(TopicsResponse, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedTopics = Table.ExpandRecordColumn(TopicsTable, "Column1",
{"guid", "topic_type", "topic_status", "title", "labels", "labels_id", "creation_date", "creation_author", "modified_date", "modified_author", "assigned_to", "description", "due_date", "document_references"},
{"guid", "topic_type", "topic_status", "title", "labels", "labels_id", "creation_date", "creation_author", "modified_date", "modified_author", "assigned_to", "description", "due_date", "document_references"})
in
ExpandedTopics,
// Load project data
Projects = GetProjects(),
// Dynamically load topics for each project
Topics = Table.AddColumn(Projects, "Topics", each GetTopics([project_id])),
// Expand topics into separate columns
ExpandedTopics = Table.ExpandTableColumn(Topics, "Topics", {"guid", "topic_type", "topic_status", "title", "labels", "labels_id", "creation_date", "creation_author", "modified_date", "modified_author", "assigned_to", "description", "due_date", "document_references"},
{"Topic GUID", "Topic Type", "Topic Status", "Title", "Labels", "Labels ID", "Creation Date", "Creation Author", "Modified Date", "Modified Author", "Assigned To", "Description", "Due Date", "Document References"}),
// Adding a custom column for the status
AddCustomStatusColumn = Table.AddColumn(ExpandedTopics, "Status", each
if [Topic Status] = null then null
else if [Topic Status] = "Closed" then "Abgeschlossen"
else if [Topic Status] = "Draft" then "Entwurf"
else if [Topic Status] = "Open" and [Due Date] <> null and DateTime.From([Due Date]) < DateTime.LocalNow() then "Überfällig"
else if [Topic Status] = "Open" then "Offen"
else [Topic Status]),
// Converting date fields to date types
ConvertToDateType = Table.TransformColumnTypes(AddCustomStatusColumn, {
{"Creation Date", type datetime},
{"Modified Date", type datetime},
{"Due Date", type datetime}
}),
// Renaming columns in the final table to German
RenameColumns = Table.RenameColumns(ConvertToDateType, {
{"project_id", "Projekt-ID"},
{"name", "Projekt-Name"},
{"Topic GUID", "Themen-GUID"},
{"Topic Type", "Themenart"},
{"Topic Status", "Themenstatus"},
{"Title", "Themen-Titel"},
{"Labels", "Kategorien"},
{"Labels ID", "Kategorien-IDs"},
{"Creation Date", "Erstellungsdatum"},
{"Creation Author", "Thema erstellt von"},
{"Modified Date", "Änderungsdatum"},
{"Modified Author", "Thema geändert von"},
{"Assigned To", "Thema zugewiesen an"},
{"Description", "Thema-Beschreibung"},
{"Due Date", "Fälligkeitsdatum"},
{"Document References", "Dokumentverweise"},
{"Status", "Status"}
})
in
RenameColumns
Thanks for the help!
