API dataset refresh failure in PBI service

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

Hi @jmaikido - Not have a solution for this. Will wait till next week and see if someone provide a response else will mark it closed.

Thanks
Ankit J