PowerBI查询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 Advanced Editor中显示的错误屏幕截图

最近两天的大部分时间里，我试图使用此方法或文档查找一些示例。 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/zh-CN/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-conten ts ）


• Json.FromValue（ https://docs.microsoft.com/zh-cn/powerquery-m/json-fromvalue ）


• Json.Document（ https://docs.microsoft.com/zh-cn/powerquery-m/json-文档）



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查询WebMethod.Post返回Expression.Error：我们无法将值“ POST”转换为输入功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！