PowerBI Query WebMethod.Post 返回 Expression.Error:我们无法转换值“POST"；键入函数 [英] PowerBI Query WebMethod.Post returns Expression.Error: We cannot convert the value "POST" to type Function

问题描述

我使用的网站要求使用 Webform.Post 方法提交他们的 API 密钥和查询数据.我能够让它在 Python、C# 中工作，我什至能够构造和执行一个 cURL 命令，该命令返回一个 Excel 可以解析的可用 JSON 文件.我也在使用 Postman 来验证我的参数，并且使用所有这些方法看起来一切都很好.但是，我的目标是构建一个可以在 Excel 中使用但在 PowerBi Query 中无法通过此查询语法的查询表单.

I'm using a website that requires that their API key AND query data be submitted using Webform.Post method. I'm able to get this to work in Python, C# and I'm even able to construct and execute a cURL command which returns a usable JSON file that Excel can parse. I am also using Postman to validate my parameters and everything looks good using all these methods. However, my goal is to build a query form that I can use within Excel but I can't get past this query syntax in PowerBi Query.

现在我正在做一个简单的查询.该查询如下所示:

For now I am doing a simple query. That query looks like this:

let
    url_1 = "https://api.[SomeWebSite].com/api/v1.0/search/keyword?apiKey=blah-blah-blah",

    Body_1 = {
            
            "SearchByKeywordRequest: 
            
            {
                ""keyword"": ""Hex Nuts"",
                ""records"": 0,
                ""startingRecord"": 0,
                ""searchOptions"": Null.Type,
                ""searchWithYourSignUpLanguage"": Null.Type
            }"
            
             },
    
    Source = WebMethod.Post(url_1,Body_1)
    
in
    Source

显示有效语法的屏幕截图

它会产生以下错误:

Expression.Error: We cannot convert the value "POST" to type Function.
Details:
    Value=POST
    Type=[Type]

PowerQuery 高级编辑器中出现的错误屏幕截图

在过去两天的大部分时间里，我都在尝试使用此方法或文档查找一些示例.Microsoft 文档简单说明如下:

I've spend the better part of the last two days trying to find either some example using this method or documentation. The Microsoft documentation simple states the follow:

WebMethod.Post
04/15/2018
2 minutes to read

About
Specifies the POST method for HTTP.

https://docs.microsoft.com/en-us/powerquery-m/webmethod-post

这无济于事，到目前为止，我发现的唯一帖子批评海报没有使用 GET 与 POST.我会这样做，但我正在使用的网站不支持.如果有人能指点我一份解释我做错了什么的文件或提出解决方案，我将不胜感激.

This is of no help and the only posts I have found so far criticize the poster for not using GET versus POST. I would do this but it is NOT supported by the website I'm using. If someone could just please either point me to a document which explains what I am doing wrong or suggest a solution, I would be grateful.

推荐答案

WebMethod.Post 不是函数.它是一个常量文本值POST".您可以使用 Web.Contents 或 WebAction.Request 函数发送 POST 请求.

WebMethod.Post is not a function. It is a constant text value "POST". You can send POST request with either Web.Contents or WebAction.Request function.

一个发布 JSON 并接收 JSON 的简单示例:

A simple example that posts JSON and receives JSON:

let
    url = "https://example.com/api/v1.0/some-resource-path",
    headers = [#"Content-Type" = "application/json"],
    body = Json.FromValue([Foo = 123]),
    source = Json.Document(Web.Contents(url, [Headers = headers, Content = body])),
    ...

于 19 年 11 月 14 日添加

请求体必须是二进制类型，并作为Web.Contents函数第二个参数的Content字段.

Added Nov 14, 19

Request body needs to be a binary type, and included as Content field of the second parameter of Web.Contents function.

您可以使用 Json.FromValue 函数构造二进制 JSON 值.相反，您可以使用 Json.Document 函数将二进制 JSON 值转换为对应的 M 类型.

You can construct a binary JSON value using Json.FromValue function. Conversely, you can convert a binary JSON value to a corresponding M type using Json.Document function.

注意{}是M语言中的list类型，类似于JSON数组.[] 是 record 类型，类似于 JSON 对象.

Note {} is list type in M language, which is similar to JSON array. [] is record type, which is similar to JSON object.

话虽如此，您的查询应该是这样的，

With that said, your query should be something like this,

let
    url_1 = "https://api.[SomeWebSite].com/api/v1.0/search/keyword?apiKey=blah-blah-blah",

    Body_1 = Json.FromValue([
        SearchByKeywordRequest = [
            keyword = "Hex Nuts",
            records = 0,
            startingRecord = 0,
            searchOptions = null,
            searchWithYourSignUpLanguage = null
        ]
    ]),

    headers = [#"Content-Type" = "application/json"],

    source = Json.Document(Web.Contents(url_1, [Headers = headers, Content = Body_1])),

    ...

参考:

• Web.Contents (https://docs.microsoft.com/zh-cn/powerquery-m/web-contents)
• Json.FromValue (https://docs.microsoft.com/zh-cn/powerquery-m/json-fromvalue)
• Json.Document (https://docs.microsoft.com/zh-cn/powerquery-m/json-document)

References:

• Web.Contents (https://docs.microsoft.com/en-us/powerquery-m/web-contents)
• Json.FromValue (https://docs.microsoft.com/en-us/powerquery-m/json-fromvalue)
• Json.Document (https://docs.microsoft.com/en-us/powerquery-m/json-document)

这篇关于PowerBI Query WebMethod.Post 返回 Expression.Error:我们无法转换值“POST"；键入函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！