calling custom function that uses Web.Contents to return a table against multiple rows

Question

I have written a custom function called GetTableFromAPI2 that utilizes an internal API to return a table. I would like to be able to run this against multiple rows from my source table. The function is as follows:

(input as any, SUBSKEY as text, token as text) as table =>
let
    json = Record.Field(input,"JSON"),

    jsonBinary = Text.ToBinary(json),
    URL = Record.Field("API"),
    options = [Headers = [ #"Ocp-Apim-Subscription-Key"= SUBSKEY,
                #"Content-Type"="application/json",
                #"Authorization" = token
            ],
            Content = jsonBinary],
    WebContent     = Web.Contents(URL, options),
    FormatAsJson   = Json.Document(WebContent),
    ConvertToTable = Record.ToTable(FormatAsJson),
    TransposeTable = Table.Transpose(ConvertToTable),
    PromoteHeaders = Table.PromoteHeaders(TransposeTable,[PromoteAllScalars=true])
in
    PromoteHeaders

This is a screen shot of the table that I am working with (reduced to one row for troubleshooting): sample table

I thought that I would be able to call this in this way (SUBSKEY and OAuthCurrentKey are both defined parameters):

each GetTableFromAPI2(_, SUBSKEY, OAuthCurrentKey)

But when I try that I get prompted for the input parameter.

In addition to the above, I've also tried to troubleshoot it by passing the row specifically to the function:

= GetTableFromAPI2(Source2{0}, SUBSKEY, OAuthCurrentKey)

This yields the following error: An error occurred in the ‘’ query. Expression.Error: 1 arguments were passed to a function which expects 2.

I do have this working without the function so I could create separate queries for each of my source rows but that is less than ideal.

Answer 1

Your calling code should look like this

#"Invoked Custom Function" = Table.AddColumn(#"Previous Step", 
    "GetTableFromAPI2", each GetTableFromAPI2([Input], [SUBSKEY], [Token])
)

and then you expand that GetTableFromAPI2 column.