PowerQuery调用POST API报错：JSON输入末尾存在多余字符

问题重现 #

我使用以下PowerQuery代码调用SCB的API：

let
    url = "https://api.scb.se/OV0104/v1/doris/en/ssd/BO/BO0101/BO0101G/LghHustypKv",
    headers = [#"Content-Type"="application/json"],
    body = "{
            ""query"": [{""code"":""Region"",
                        ""selection"":{""filter"":""item"",""values"":[""00""]}},
                        {""code"":""Hustyp"",
                        ""selection"":{""filter"":""item"",""values"":[""1113"",""21""]}}],
            ""response"": {""format"":""csv""}
            }",
    Source = Json.Document(Web.Contents(url,[Headers = headers,Content = Text.ToBinary(body)]))
in
    Source

在Postman中可正常运行，但在Excel的PowerQuery中收到错误：

DataFormat.Error: We found extra characters at the end of the JSON input.
Details:
Value=,
Position=8

问题原因 #

代码里明确指定了response.format为csv，API返回的是CSV格式文本，但你用Json.Document()去解析非JSON结构的内容，自然会触发格式错误。

解决方案 #

方案1：解析CSV格式返回值 #

将解析方式替换为Csv.Document()，匹配API返回的格式：

let
    url = "https://api.scb.se/OV0104/v1/doris/en/ssd/BO/BO0101/BO0101G/LghHustypKv",
    headers = [#"Content-Type"="application/json"],
    body = "{
            ""query"": [{""code"":""Region"",
                        ""selection"":{""filter"":""item"",""values"":[""00""]}},
                        {""code"":""Hustyp"",
                        ""selection"":{""filter"":""item"",""values"":[""1113"",""21""]}}],
            ""response"": {""format"":""csv""}
            }",
    csvContent = Web.Contents(url,[Headers = headers,Content = Text.ToBinary(body)]),
    Source = Csv.Document(csvContent)
in
    Source

方案2：修改API返回格式为JSON #

如果需要JSON格式的数据，把请求体里的""format"":""csv""改成""format"":""json""，继续用Json.Document()解析：

let
    url = "https://api.scb.se/OV0104/v1/doris/en/ssd/BO/BO0101/BO0101G/LghHustypKv",
    headers = [#"Content-Type"="application/json"],
    body = "{
            ""query"": [{""code"":""Region"",
                        ""selection"":{""filter"":""item"",""values"":[""00""]}},
                        {""code"":""Hustyp"",
                        ""selection"":{""filter"":""item"",""values"":[""1113"",""21""]}}],
            ""response"": {""format"":""json""}
            }",
    Source = Json.Document(Web.Contents(url,[Headers = headers,Content = Text.ToBinary(body)]))
in
    Source

内容的提问来源于stack exchange，提问作者getFEA