Hi folks,
I have a report that accesses data via an API. Manual refresh in the PBI desktop completes successfully. The API code requires un/pwd/clientkey. When the report is published, it cannot refresh since I am unable to create a data connection to be used by our gateway. The embedded url referenced within the dataset that is used when trying to create the new connection does not have provision for 3 creds. ie. un/pwd and clientkey. I have read that i may need a custom data connector. Can anyone advise. here is the working code.
let
// Step 1: Login - ClientKey for Roads
//LoginUrl = "https://api.civicagov.com/api/DataService/Login/json",
LoginUrl = "https://reflect.civicagov.com/api/DataService/Login/json",
LoginBody = Text.ToBinary("{""UserName"": ""LismoreApiUser"", ""Password"": ""IrC@t!C3LiGHTiNj"", ""ClientKey"": ""ZDlmMTNjNzAtOGM4OS00ZjY1LWJkY2MtMjY2M2QxODgwOTFh0""}"),
LoginResponse = Json.Document(
Web.Contents(
"https://api.civicagov.com",
//"https://reflect.civicagov.com",
[
RelativePath = "api/DataService/Login/json",
Content = LoginBody,
Headers = [#"Content-Type" = "application/json"]
]
)
),
UserId = LoginResponse[UserId],
SessionId = LoginResponse[SessionId],
// Step 2: Define batch function inline
GetBatch = (skip as number) =>
let
RequestBody = Json.FromValue([
UserId = UserId,
SessionId = SessionId,
ModuleName = "Defects",
FilterId = "00000000-0000-0000-0000-000000000000",
Skip = skip,
Take = 1000,
Data = {
[
Name = "RequestType",
Type = "Equal",
Value = "PUBLIC"
]
}
]),
Response = Json.Document(
Web.Contents(
"https://api.civicagov.com",
[
RelativePath = "api/DataService/FindRecords/json",
Content = RequestBody,
Headers = [#"Content-Type" = "application/json"]
]
)
),
Records = Response[Data]
in
Records,
// Step 3: Generate skip list and retrieve all batches
SkipList = List.Generate(() => 0, each _ < 11000, each _ + 1000),
AllBatches = List.Transform(SkipList, each GetBatch(_)),
CombinedRecords = List.Combine(AllBatches),
// Step 4: Convert to table
FinalTable = Table.FromRecords(CombinedRecords),
TransformDateColumn = Table.TransformColumns(FinalTable,{{"CompletionDate", each Date.FromText(Text.Start(_, 10)), type date},
{"CreatedOn", each Date.FromText(Text.Start(_, 10)), type date},
{"DateRaised", each Date.FromText(Text.Start(_, 10)), type date},
{"InitialAccomplishmentDate", each Date.FromText(Text.Start(_, 10)), type date},
{"ModifiedOn", each Date.FromText(Text.Start(_, 10)), type date},
{"TargetDate", each Date.FromText(Text.Start(_, 10)), type date}})
in
TransformDateColumn
TIA
Warm regards, John