调用自定义函数,该函数使用 Web.Contents 返回针对多行的表格。

huangapple go评论66阅读模式
英文:

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

问题

我已经编写了一个名为GetTableFromAPI2的自定义函数,该函数利用内部API返回一个表格。我希望能够对来自源表的多行运行此函数。函数如下:

(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

这是我正在处理的表格的屏幕截图(为了故障排除而缩减为一行):

sample table

我以为我可以这样调用它(SUBSKEY和OAuthCurrentKey都是已定义的参数):

each GetTableFromAPI2(_, SUBSKEY, OAuthCurrentKey)

但是当我尝试这样做时,我被要求输入参数。

除上述方法之外,我还尝试通过将特定行传递给函数来进行故障排除:

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

这会产生以下错误:

“查询”中发生错误。Expression.Error: 已将 1 个参数传递给一个需要 2 个参数的函数。

我已经将此功能工作正常,不使用函数,所以我可以为每个源行创建单独的查询,但这不是理想的解决方法。

英文:

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.

答案1

得分: 0

你的调用代码应该如下所示:

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

然后你扩展那个 GetTableFromAPI2 列。

英文:

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.

huangapple
  • 本文由 发表于 2023年2月27日 18:46:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/75579453.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定