Custom Connectors - API url + body

[damd]

Hi,

I am working on a custom connector for PowerBI to get Data from an API. I'd like to specify both the url and the json body

I created a working version that only takes the url as parameter. Everything else is hardcoded.

MyQueryType = type function (
url as (Uri.Type meta [
        Documentation.FieldCaption = "Instance Uri",
        Documentation.FieldDescription = "Enter your Instance url",
        Documentation.SampleValues = { "http://my-instance.westeurope.cloudapp.azure.com" }
        ])
) as table meta [
        Documentation.Name = "My Query",
        Documentation.LongDescription = "Long Desc"
        ];

APIRequestImpl = (url as text) as table =>

    let
        url_full = url&"/library/api/invocation",
        headers = [#"Content-Type"="application/json"],
        body = "{
                ""dataframe"": """",
                ""input"": ""{\""sensor.id\"":\""abc-bourse_cotations_abcng\"",\""sensor.channel\"":\""Ouverture\"",\""start_date\"":\""2010-01-01\"",\""end_date\"":\""2030-01-01\""}"",
                ""technical_name"": ""TSDataCleaning""
            }",
        response = Json.Document(Web.Contents(url_full, [Content=Text.ToBinary(body), Headers=headers])),
        toTable = Table.FromList(response[preprocessing_data], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        expand = Table.ExpandRecordColumn(toTable, "Column1", {"timestamps", "value", "imputed_value","denoised_value","is.missing","is.anomaly"}, {"timestamps", "value", "imputed_value","denoised_value","is.missing","is.anomaly"})
               
    in
        expand;

But when I try to add the request body parameter, It get this error:

My code:

MyQueryType = type function (
url as (Uri.Type meta [
        Documentation.FieldCaption = "Loamics instance Uri",
        Documentation.FieldDescription = "Enter your LOAMICS Instance url",
        Documentation.SampleValues = { "http://dtles-loamics-demo-dns-mmburs3gaabwc.westeurope.cloudapp.azure.com" }
        ]),
request_body as (type text meta [
        Documentation.FieldCaption = "Request body",
        Documentation.FieldDescription = "Enter your request body",
        Documentation.SampleValues = { "{}" }])
) as table meta [
        Documentation.Name = "My Query",
        Documentation.LongDescription = "Long Desc"
        ];

APIRequestImpl = (url as text, request_body as text) as table =>

    let
        url_full = url&"/library/api/invocation",
        headers = [#"Content-Type"="application/json"],
        body = "{
                ""dataframe"": """",
                ""input"": ""{\""sensor.id\"":\""abc-bourse_cotations_abcng\"",\""sensor.channel\"":\""Ouverture\"",\""start_date\"":\""2010-01-01\"",\""end_date\"":\""2030-01-01\""}"",
                ""technical_name"": ""TSDataCleaning""
            }",
        response = Json.Document(Web.Contents(url_full, [Content=Text.ToBinary(body), Headers=headers])),
        toTable = Table.FromList(response[preprocessing_data], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        expand = Table.ExpandRecordColumn(toTable, "Column1", {"timestamps", "value", "imputed_value","denoised_value","is.missing","is.anomaly"}, {"timestamps", "value", "imputed_value","denoised_value","is.missing","is.anomaly"})
               
    in
        expand;

Thanks in advance

[damd · Accepted solution]

I found a workaround by setting request_body as optional, although this field is mandatory in this case...

[v-yanjiang-msft]

Hi @damd ,

Glad you solve the problem by yourself, would you like accept your workaroud as solution? More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[mkenn]

Hi @v-yanjiang-msft ,

is there any way to make the second field (in this case the request_body) mandatory or must all fields (except of the url field) be optional by default?

Best regards,

mkenn

[v-yanjiang-msft]

Hi @damd ,

From the error message can see, it seems related with the Loamics data source.

Have you ever saw the same error with other data source.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.