Hi,
I have been working on a custom data connector and have ran into some issues with authentication.
The first time the connector is run on a machine it always works correctly, but if it is run a second time users get the error "We couldn't authenticate with the credentials provided, please try again." and the only way to resolve this seems to be clearing the cache data in Power Bi.
The connector uses the UsernamePassword authenication method, It also requires a few paramters, all of which I have excluded from the data source path to ensure that is not the issue. It then runs some queries on a REST api to get the required data.
I have included the code for the first draft of the connector below, any help would be much appreciated.
// This file contains your Data Connector logic
section Custom_Connector_Connector;
//Current error "We couldn't authenticate with the credentials provided, please try again."
client_id = Text.FromBinary(Extension.Contents("client_id"));
//Default Entry point for the extension.
//The user will be provided with a input field for firm name, and a date range to filter the results by.
[DataSource.Kind="Custom_Connector_Connector", Publish="Custom_Connector_Connector.Publish"]
shared Custom_Connector_Connector.Contents = Value.ReplaceType(Custom_Connector_ConnectorImpl, Custom_Connector_ConnectorType);
Custom_Connector_ConnectorType = type function (
#"Firm Name" as (type text meta [
DataSource.Path = false
]),
From as (type date meta [
DataSource.Path = false
]),
To as (type date meta [
DataSource.Path = false
]))
as table;
Custom_Connector_ConnectorImpl = (#"Firm Name" as text, From as date, To as date) => //, From as datetime, To as datetime
let
//We log the user in via the api and get the access token to run our queries.
AuthData = TokenMethod(),
//We then run a predefined query to get all of the call data for the specified firm between the specified dates.
AllData = GetAllData(AuthData[access_token], #"Firm Name",From, To),
//We transform the simple Table of json data into the structure we want.
TransformedData = TransformTable(AllData)
in
//Return the final table of data.
TransformedData;
// Data Source Kind description
Custom_Connector_Connector = [
Authentication = [
UsernamePassword = []
],
Label = Extension.LoadString("DataSourceLabel")
];
// Data Source UI publishing description
Custom_Connector_Connector.Publish = [
Beta = true,
Category = "Online Services",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = Custom_Connector_Connector.Icons,
SourceTypeImage = Custom_Connector_Connector.Icons
];
Custom_Connector_Connector.Icons = [
Icon16 = { Extension.Contents("Custom_Connector_Connector16.png"), Extension.Contents("Custom_Connector_Connector20.png"), Extension.Contents("Custom_Connector_Connector24.png"), Extension.Contents("Custom_Connector_Connector32.png") },
Icon32 = { Extension.Contents("Custom_Connector_Connector32.png"), Extension.Contents("Custom_Connector_Connector40.png"), Extension.Contents("Custom_Connector_Connector48.png"), Extension.Contents("Custom_Connector_Connector64.png") }
];
/*
TokenMethod
Returns:
Json Document.
Desc:
Method makes a POST request to API to log the user account in and get an access token.
Manual credentials must be true here or the Power Query API will automatically add an authorization header that will break the request.
*/
TokenMethod = () =>
let
Response = Web.Contents("https://some.api.com", [
IsRetry = true,
ManualCredentials = true,
Headers=[#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
RelativePath = "/Production/web/oauth/login",
Content = Text.ToBinary(Uri.BuildQueryString([
client_id = client_id,
grant_type = "password",
scope= "session-type:Analyst",
password = Extension.CurrentCredential()[Password],
username = Extension.CurrentCredential()[Username]
]))]),
Part = Json.Document(Response)
in
Part;
/*
GetAllData
Returns:
Table.
Desc:
Function gets all of the paginated data from the API.
*/
GetAllData = (accessToken, FirmName, From, To) =>
let
FirstResponse = QueryFirstPage(accessToken, FirmName, From, To),
//We will use count to determine how many requests are needed.
//We default to 100 entries per page.
TotalCount = FirstResponse[#"__count"],
FirstPage = Table.FromRecords({FirstResponse}),
AllData = if (TotalCount > 100) then QueryPage(accessToken,FirmName, From, To,TotalCount, FirstPage, 100) else FirstPage
in
AllData;
QueryFirstPage = (accessToken, FirmName, From, To) =>
let
DateFrom = Text.From(Date.Year(From))&"-"&Text.PadStart(Text.From(Date.Month(From)),2,"0")&"-"&Text.PadStart(Text.From(Date.Day(From)),2,"0"),
DateTo = Text.From(Date.Year(To))&"-"&Text.PadStart(Text.From(Date.Month(To)),2,"0")&"-"&Text.PadStart(Text.From(Date.Day(To)),2,"0"),
uri = Uri.Parts("https://some.api.com?"&Uri.BuildQueryString([
#"$select" = "Ref,CreatedDate,Priority.Name,User.Name,Organization.Name",
#"$filter" = "CreatedDate >= @DateTime("&DateFrom&"T00:00:00.000Z) && CreatedDate < @DateTime("&DateTo&"T00:00:00.000Z) && (Organization.Name="""&FirmName&""" || Organization.Parent.Name="""&FirmName&""")",
#"$top"= "100",
#"$inlinecount" = "true"
])),
Response = Web.Contents("https://some.api.com", [
ManualCredentials = true,
Headers=[#"Accept" = "*/*", #"Authorization" = "Bearer " & accessToken],
Query = uri[Query],
RelativePath = "/Production/api/v2/call"
]),
Page = Json.Document(Response)
in
Page;
QueryPage = (accessToken, FirmName, From, To, count, previousTable, skip) =>
let
DateFrom = Text.From(Date.Year(From))&"-"&Text.PadStart(Text.From(Date.Month(From)),2,"0")&"-"&Text.PadStart(Text.From(Date.Day(From)),2,"0"),
DateTo = Text.From(Date.Year(To))&"-"&Text.PadStart(Text.From(Date.Month(To)),2,"0")&"-"&Text.PadStart(Text.From(Date.Day(To)),2,"0"),
uri = Uri.Parts("https://some.api.com?"&Uri.BuildQueryString([
#"$select" = "Ref,CreatedDate,Priority.Name,User.Name,Organization.Name",
#"$filter" = "CreatedDate >= @DateTime("&DateFrom&"T00:00:00.000Z) && CreatedDate < @DateTime("&DateTo&"T00:00:00.000Z) && (Organization.Name="""&FirmName&""" || Organization.Parent.Name="""&FirmName&""")",
#"$top"= "100",
#"$skip" = Text.From(skip)
])),
Response = Web.Contents("https://some.api.com", [
ManualCredentials = true,
Headers=[#"Accept" = "*/*", #"Authorization" = "Bearer " & accessToken],
Query = uri[Query],
RelativePath = "/Production/api/v2/call"
]),
Page = Json.Document(Response),
CurrentTable = Table.FromRecords({Page}),
NextTable = Table.Combine({
previousTable,
CurrentTable
})
in
if (count > skip + 100) then
QueryPage(accessToken,FirmName,From, To, count, NextTable, skip + 100)
else
NextTable;
TransformTable = (json) =>
let
#"Expanded results" = Table.ExpandListColumn(json, "results")
// Do some transformations here
in
#"results";
