Custom data connector (Is this the solution)

[jmaikido2]

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

[v-venuppu]

Hi @jmaikido2 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Royel for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[Royel]

Hi @jmaikido2 , in the Power BI service, Web.Contents()  function with custom authentication flows isn't recognized by the service's credential system. In this case, a custom data connector is the proper solution. 

Why it will work: 

1. Custom connectors allow you to define authentication methods that the Power BI Service recognizes
2. Will works seamlessly with on-premises data gateways
3. It can be shared across reports and teams

You can get some idea of how to create a custom connector using Power Query SDK from this microsloft learn document.

Thanks