Power Query，使用表单数据进行http POST请求

Question

我是一位有用的助手，可以为您进行文本翻译。

我有一个REST API，只接受带有表单数据的POST请求。

我知道在Power Query中，JSON请求是这样的：

let
    url = "https://example.com",
    body = "{ ""first_param"": ""AAAAA"",  ""second_param"": ""BBBBBB""}",    

    Source = Json.Document(Web.Contents(url,[ Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] ))
in
    Source

如何发送表单数据？

- Mr.D

---

经过简短的谷歌搜索后，一个表单的POST请求以“application/x-www-form-urlencoded”或“multipart/form-data”的格式在正文中发送。因此，我会查找如何生成与这些格式之一匹配的数据，并将其放入Web.Contents请求的Content参数中。 - Wedge

Answer 1

使用Uri.BuildQueryString和Json.Document

let
    url = "https://example.com",
    body  = "{ ""first_param"": ""AAAAA"",  ""second_param"": ""BBBBBB""}",
    Parsed_JSON = Json.Document(body),
    BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
    Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(BuildQueryString) ] ))
in
    Source

顺便提一下，最好直接在记录中构建body，避免使用文本字符串和双引号的问题。

- Sergey Lossev

---

你能否解释一下如何在请求体中传递当前日期？在我的情况下，我有一个名为_fromDate_和_toDate_的_first_和_second_参数。 - Guu

DateTime.LocalNow() 等于 2013-03-08T14:22:42。 - Sergey Lossev

类似这样的代码 = DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd") 等同于 2018-08-29 - Sergey Lossev

Answer 2

我使用这种方式，它可以工作。授权类型是基本的，并且用户名和密码已编码。

let
  url = "http://localhost:8091/_p/query/query/service?",

  body = "{
    ""statement"": ""SELECT ROUND((SUM(src.DUR) / COUNT(.)), 0) AS 'Mean Screen Time per day' FROM \r\n(SELECT
                  SUM(TONUMBER(source.DURATION)) AS DUR, source.startDate AS DATE FROM \r\n(SELECT startDate, DATE_DIFF_STR(completionDate,
                  startDate, 'second') AS DURATION, attributes.screen AS SCREEN \r\nFROM data WHERE type_ = \""Event\"" AND type is NOT MISSING and
                  startDate IS NOT MISSING and completionDate IS NOT MISSING \r\nand completionDate > startDate and attributes.screen IS NOT
                  MISSING) source GROUP BY source.startDate) src"",
                  ""pretty"":true,""timeout"":""600s"",""profile"":""timings"",""scan_consistency":"not bounded"",""client_context_id"":""xyz""}",

  Source = Json.Document(Web.Contents(
    url,[
        Timeout=#duration(0,0,120,0),
        Headers=[#"Authorization"="Basic YXB",#"Content-Type"="application/json"],
        Content=Text.ToBinary(body)
        ]
      )
    ),

  results = Source[results],

  #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"MEAN _DURATION", "SCREEN"},{"MEAN DURATION","SCREEN"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"MEAN_DURATION", type number}})

in

  #"Changed Type"

- KARTHIKEYAN.A

---

我认为如果您将这段代码作为代码块写在答案中，它会更有用。这将有助于其他用户在未来测试您的答案。 - Mr.D

当然可以 :) @Mr.D - KARTHIKEYAN.A

Answer 3

对于最新版本，这个方法在POST方法API上运行良好，因为我尝试从Zoho API中获取访问令牌以传递给GET API。

let

SvcUrl = "https://****",

Content = "
{
 
  ""Tenant"": daenet"",
  ""filter"": {
    ""fields"": [
      {
        ""fieldName"": ""Field1"",
        ""operator"": 0,
        ""argument"": [
          ""003""
        ],
        ""distinct"": false
      }
    ]
  },
  ""fields"": [
    ""ProjectedField1"",""ProjectedField2"", ""EngTyp"", ""Name""
  ],
  ""top"": 100,
  ""skip"": 0,
  ""language"": ""001""
}
",

Response= Web.Contents(SvcUrl, 

[
Content=Text.ToBinary(Content),
Headers=[#"Content-Type" = "application/json"]
]
),

Json = Json.Document(Response)

in

  Json

对于没有内容的API，请使用Content = "{}"

参考 - https://developers.de/2021/10/11/how-to-send-the-post-request-in-powe-query/

- Viraj Sandaruwan