Power Query REST API with Token and additional value - How to add this additional value?!

[Chiniminiz]

Hello everybody,

we try to connect to a data source via REST API with the Post method and Web.Content().

This source can be normally reached and set up via browser. Here we created a reporting user and regarding to a user manual we received from the developer we created a token for a "X-Best-Auth" verification/authentification.

This manual can be officially reached on following link (hopefully I may post it here, as it shoud be no advertisement):

https://www.cordaware.com/help_english/index.html?api.htm

In the manual is an extra part for REST API under Interfaces.

We did everything regarding to the user manual and have now following parts:

URL to Server

X-Best-Auth Authentification code

We tested everything in Curl on Linux and got a connection to the tables.

Unfortunately we receive errors in Power Query regarding to a missing part.

Our query looks like following (including the correct credentials):

let
    
    url = "https://bestinformed.#/rest",
    apikeyname = "X-Best-Auth",
    headerXbestauth = "ABC123==",   // not the correct one
    headerOrigin = "PC123",
    content = "PostJson",
    // something with {"action":"running"}

    GetJsonQuery = Web.Contents(url, 
    [
    // Content=Json.FromValue( ),
    Headers=
        [#"X-Best-Auth"=headerXbestauth, Origin=headerOrigin]
    ]),
    // ,TextEncoding.Utf8)

    FormatAsJsonQuery = Json.Document(GetJsonQuery)

in
    FormatAsJsonQuery

Addition to the code: something is included, but won't be used like the <content="PostJson"> part.

We reached the server and now we receive error (500), which stands for "an error that originates from the server has occurred".

Following the user manual we have to tell the server at the end something like

{"action":"running"}

which tells him, what a have to show or to filter. So a path-like addition, but we don't know how to put this part into the query.

Can someone help us?

[Chiniminiz]

Hi v-jingzhang,

with the video, other sources and a little bit try and error I got the connection.

The code is 

let
    Source = 
    Json.Document(
        Web.Contents(
            LoadURL,
            [
                Headers=[#"Content-Type"="application/json",
                #"Origin"= "Servername.domain.de", 
                #"X-Best-Auth" = "#SecurityKey"],
                Content= info
            ]
        )
    ),
    
in
    Source

The problem was, to give the REST API the correct content (Body). This must be in a JSON format. So we needed a Table, which has to be converted into JSON format, which forward to the API in the Content.

Also the Origin and the Auth-Code has to be used in the Header and not like usually in the Content.

Thanks for your help and hopefully I help with this post others with an similar problem.

[v-jingzhang · Accepted solution]

Hi @Chiniminiz 

It seems this additional part should also be included in the Content value. See if these videos help:

Making a POST Request Using Power Query - YouTube

Send API POST Requests in Excel with Power Query - YouTube

Something like

Content=Json.FromValue({[xxx="xxxxx", action="running"]})

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.