API dataset refresh failure in PBI service

jmaikido · September 1, 2025, 3:41am

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

ankit · September 5, 2025, 5:32am

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

ShehabElSaghier · September 8, 2025, 12:40am

Maybe try to split it into multiple queries

// Auth_Query
// Auth_Query
let
BaseUrl = “https://api.civicagov.com”,
LoginBody = Text.ToBinary(“{”“UserName”“: ““LismoreApiUser””, ““Password””: ““IrC@t!C3LiGHTiNj””, ““ClientKey””: ““ZDlmMTNjNzAtOGM4OS00ZjY1LWJkY2MtMjY2M2QxODgwOTFh0””}”),
LoginResponse = Json.Document(
Web.Contents(
BaseUrl,
[
RelativePath = “api/DataService/Login/json”,
Content = LoginBody,
Headers = [#“Content-Type” = “application/json”]
]
)
)
in
LoginResponse

// Data_Query
// Data_Query
let
// Reference the auth query
AuthData = Auth_Query,
UserId = AuthData[UserId],
SessionId = AuthData[SessionId],
BaseUrl = “https://api.civicagov.com”,

// Batch function
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(
                BaseUrl,
                [
                    RelativePath = "api/DataService/FindRecords/json",
                    Content = RequestBody,
                    Headers = [#"Content-Type" = "application/json"]
                ]
            )
        ),
        Records = Response[Data]
    in
        Records,

// Generate batches
SkipList = List.Generate(() => 0, each _ < 11000, each _ + 1000),
AllBatches = List.Transform(SkipList, each GetBatch(_)),
CombinedRecords = List.Combine(AllBatches),
FinalTable = Table.FromRecords(CombinedRecords)

in
FinalTable

// Data_Query
// Data_Query
let
// Reference the auth query
AuthData = Auth_Query,
UserId = AuthData[UserId],
SessionId = AuthData[SessionId],
BaseUrl = “https://api.civicagov.com”,

// Batch function
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(
                BaseUrl,
                [
                    RelativePath = "api/DataService/FindRecords/json",
                    Content = RequestBody,
                    Headers = [#"Content-Type" = "application/json"]
                ]
            )
        ),
        Records = Response[Data]
    in
        Records,

// Generate batches
SkipList = List.Generate(() => 0, each _ < 11000, each _ + 1000),
AllBatches = List.Transform(SkipList, each GetBatch(_)),
CombinedRecords = List.Combine(AllBatches),
FinalTable = Table.FromRecords(CombinedRecords)

in
FinalTable

// Auth_Query
// Auth_Query
let
BaseUrl = “https://api.civicagov.com”,
LoginBody = Text.ToBinary(“{”“UserName”“: ““LismoreApiUser””, ““Password””: ““IrC@t!C3LiGHTiNj””, ““ClientKey””: ““ZDlmMTNjNzAtOGM4OS00ZjY1LWJkY2MtMjY2M2QxODgwOTFh0””}”),
LoginResponse = Json.Document(
Web.Contents(
BaseUrl,
[
RelativePath = “api/DataService/Login/json”,
Content = LoginBody,
Headers = [#“Content-Type” = “application/json”]
]
)
)
in
LoginResponse

// Final_Query
// Final_Query
let
RawData = Data_Query,
TransformDateColumn = Table.TransformColumns(RawData, {
{“CompletionDate”, each if _ <> null then Date.FromText(Text.Start(, 10)) else null, type nullable date},
{“CreatedOn”, each if _ <> null then Date.FromText(Text.Start(, 10)) else null, type nullable date},
{“DateRaised”, each if _ <> null then Date.FromText(Text.Start(, 10)) else null, type nullable date},
{“InitialAccomplishmentDate”, each if _ <> null then Date.FromText(Text.Start(, 10)) else null, type nullable date},
{“ModifiedOn”, each if _ <> null then Date.FromText(Text.Start(, 10)) else null, type nullable date},
{“TargetDate”, each if _ <> null then Date.FromText(Text.Start(, 10)) else null, type nullable date}
})
in
TransformDateColumn