REST API POST Request works in Postman, Fails in Power Query

[Duftnich06]

I have an REST API that when I send a POST request via Postman, it works successfully, but refuses to work when sending via Power Query, M Code below (sensitive details omitted):

let
    url = "exampleurl.com/api",
    headers = [Authorization = "OAuth realm=""12345"",oauth_consumer_key=""OAuthConsumerKeyLotsOfCharacters"",oauth_token=""OAuthTokenLotsOfCharacters"",oauth_signature_method=""HMAC-SHA256"",oauth_timestamp=""exampletimestamp"",oauth_nonce=""examplenonce"",oauth_version=""1.0"",oauth_signature=""OAuthSignatureLotsOfCharacters""", #"Content-Type" = "application/json"],
    postData = Json.FromValue({[searchID="customsearch_active_customers"]}),
    response = Web.Contents(
        url,
        [
            Headers = headers,
            Content = postData
        ]
    ),
    jsonResponse = Json.Document(response),
    #"error" = jsonResponse[error]
in
    #"error"

It appears to connect successfully though, as the error returned is one defined by the REST API.

REST API error handling:

if ( ( typeof request.searchID == 'undefined' ) || ( request.searchID === null ) || ( request.searchID == '' ) ) {		
			throw { 'type': 'error.SavedSearchAPIError', 'name': 'INVALID_REQUEST', 'message': 'No searchID was specified.' }			
		}

 Power Query error response:

As you can see the error returned is the REST API's error handling.

What this suggests to me is that the 'Json.FromValue' is not working and is, infact, evaluating to one of the 3 expected values in the REST API's error handling, but I can't seem to stop it from doing it.

I have tried the 3 below variations to the 'Json.FromValue' and I get 'Access to Resources Forbidden' error messages returned with each. The only one that doesn't return that is the 'Json.FromValue':

    postData = Json.Document("{""searchID"": ""customsearch_active_customers""}"),
    
    postData = Text.ToBinary("{""searchID"": ""customsearch_active_customers""}"),
    
    postData = Json.FromValue(
        [
            data = [
                searchID = "customsearch_active_customers"
            ]
        ]
    ),

The working Postman code:

curl --location 'exampleurl.com/api' \
--header 'Authorization: OAuth realm="12345",oauth_consumer_key="OAuthConsumerKeyLotsOfCharacters",oauth_token="OAuthTokenLotsOfCharacters",oauth_signature_method="HMAC-SHA256",oauth_timestamp="exampletimestamp",oauth_nonce="examplenonce",oauth_version="1.0",oauth_signature="OAuthSignatureLotsOfCharacters"' \
--header 'Content-Type: application/json' \
--header 'Cookie: NS_ROUTING_VERSION=LAGGING' \
--data '{
    "searchID": "customsearch_active_customers"
}'

Anyone have any ideas what I'm missing?

@ImkeF I've read many of your articles relating to this topic, so I'm hoping you might shed some light on the issue?

Cheers.

Nic

[ImkeF · Accepted solution]

Hi @Duftnich06 
without the curly bracket like so:

Json.FromValue([searchID="customsearch_active_customers"])

[Duftnich06]

Hi @ImkeF 

Thanks for your help on this, this is the solution to my missing search id.

I believe my next issue is how to encode the HMAC-SHA256 signature for each refresh of the query, i.e. pass a dynamic unix timestamp, dynamic nonce and then encode it with keys etc to generate a valid signature, hence the 'access is forbidden' response. I can do the timestamp and nonce in M code, but the encoding of the signature I can't figure out, so my theory is I need to use Power Automate...question for another board.

Many thanks!

[ImkeF]

Hi @Duftnich06 ,
great to hear 🙂
Did you realize that this was also my first answer 😉
The encoding is well above my head unfortunately.

[Duftnich06]

I did, yes. Althought at that point, I wasn't aware of my encoding issue and so I was distracted by my original error!

Thanks again 😃

[ams1]

Hi @Duftnich06 ,

Have you tried the first answer from: https://stackoverflow.com/questions/62758873/power-bi-query-connecting-to-api-that-uses-hmac-authent...

?

IMO it opens an AWSOME door to use javascript libraries from PowerQuery - didn't really have many problems for this great solution so far.

Postman has builtin the crypto-js javascript lib - this is why postman works.

Please tell us if it worked 😊 and upvote it on stackoverflow also.

[ImkeF]

Not sure, but what you pass to the Json.FromValue-function here:

Json.FromValue({[searchID="customsearch_active_customers"]})

is a record within a list.
While in your sample from postman:

--data '{
    "searchID": "customsearch_active_customers"
}'

you seem to be passing a simple record into the "data" parameter (if that's json). So definitely different.

 

[Duftnich06]

Ahhh @ImkeF 
Thanks for the observation. So, what would the syntax be to pass it as a record?

[ImkeF]

Hi @Duftnich06 ,
I thought the previous error message was: "No searchID specified"?

[Duftnich06]

Hi @ImkeF 

Correct, that's the final error I landed on, but I mentioned in my original post that I tried 3 alternate solutions all resulting in 'Access Forbidden'.
As the below line resulted in the API calling back, I stuck with it as the closest solution. However, you're right it doesn't seem to think I'm passing a search id...

Json.FromValue({[searchID="customsearch_active_customers"]})

I get the feeling it is an issue with the API itself...what do you think?

[Duftnich06]

Hi @ImkeF 
Thanks for coming back to me, sadly I get the below response:

This is the same message I recieved when I tried my other 3 solutions.

Is this an Azure/Power BI issue or does it reside with my API, it's not a very helpful error message 😂

Edit:
When I force the 'Close and Load' option, this message is returned:

[ImkeF]

Hi @Duftnich06 ,
please try the following:

    postData = Json.FromValue( [searchID = "customsearch_active_customers"] )