javascript - POST API request using PowerQuery in Excel - Stack Overflow

admin管理员组

文章数量:1122832

When using the following code:

let
    url = ";,
    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

I receive the following error:

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

As I am new to this, I am not sure what's wrong. It works in Postman, but cannot get it to work in PowerQuery.

I tried to mimic what's done here:

Thanks!

When using the following code:

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

I receive the following error:

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

As I am new to this, I am not sure what's wrong. It works in Postman, but cannot get it to work in PowerQuery.

I tried to mimic what's done here: https://community.fabric.microsoft.com/t5/Power-Query/Sending-a-POST-API-request-using-PowerQuery-on-Excel-PBI-with-a/td-p/2764875

Thanks!

• javascript
• excel
• powerquery

Share Improve this question Follow edited Nov 22, 2024 at 19:51 Brian Tompsett - 汤莱恩 5,8757272 gold badges6161 silver badges133133 bronze badges asked Nov 22, 2024 at 19:34 getFEAgetFEA 111 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

It's easier to let Power Query build the raw json string for you.

The other tip is save your response before converting to JSON.

I added the AsText field. It'll let you view the raw response from the server.

let
    baseUrl = "https://api.scb.se",    
    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"
        ]
    ],
    bytes = Web.Contents( baseUrl, [
        RelativePath = "/OV0104/v1/doris/en/ssd/BO/BO0101/BO0101G/LghHustypKv",
        Headers = headers,
        Content = Json.FromValue(body)
    ]),
    Json = Json.Document( bytes ),
    AsText = Text.FromBinary( bytes )
in
    [
        Json = Json, 
        AsText = AsText
    ]

( If you can link the documentation for your Web API, it's easier to answer. )

本文标签： javascriptPOST API request using PowerQuery in ExcelStack Overflow