英文:
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
这是我正在处理的表格的屏幕截图(为了故障排除而缩减为一行):
我以为我可以这样调用它(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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论