Hi folks,
my original problem not being able to refresh a dataset that (in PBI desktop refreshed ok). In PBI desktop the connection was via an API that used a un, pwd and clientkey. this code allowed me to perform successful manual report refreshes when in the pbi desktop. when the report was published, the refresh errored, expecting an anonymous authentication. report code included. Note a gateway has been configured, but its the authentication that is required, based on the url in the dataset.
You will notice that [https://... .com] is referenced in the report dataset, however there are no credentials available for this url. the pbi service expects anonymous authentication, but this is not permitted. Research has directed me to consider creating a custom data connector, which I've not had to do before. Is a custom data connector the solution? if so, can our learned experts create such a connection and advise on its use? Note I have access to PQ SDK via Visual Studio, if this is req'd.
report code below
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
Help required.
TIA john
