Power Query REST API with Cookies

[JeffRobson]

I'm trying to extract some data from a REST API service that uses cookies to store the authentication credentials.

let
    Source = Json.Document(Web.Contents("https://app.siliconexpert.com/ProductAPI/search/authenticateUser?login=username&apiKey=password")),
    Status = Source[Status],  
    Source1 = Json.Document(Web.Contents("https://app.siliconexpert.com/ProductAPI/search/listPartSearch?partNumber=%5b%7b%22partNumber%22:%22" & PartNumber & "wt%22%7d,%7b%22partNumber%22:%22" & PartNumber & "%22%7d%5d&mode=beginwith&pageNumber=1")),
    Status1 = Source1[Status]
in
    Status1

The first call works fine, says I'm authenticated & sets 3 cookies on my machine.

However, the second call then says I'm not authenticated & fails.

I've had a look through many articles on this subject but can't figure out how to capture the cookies then pass them back to the API so it knows I'm authenticated in subsequent queries.

Can anyone assist please?

API documentation at https://www.siliconexpert.com/apidocs/

Thanks in advance 🙂

[Dev_powerBi]

Hi, 

this is giving SSL certficate verfiy fail issue, any ideas to resolve

[JeffRobson · Accepted solution]

I ended up solving my own problem with a Python workaround.

The API documentation included some Python code which I then modified slightly to produce the results I needed.

I setup a new query using the Python Script connector with the code below as the source:

from pip._vendor import requests
import pandas as pd
 
headers = {'Content-Type': 'application/x-www-form-urlencoded'}
 
s = requests.Session()
 
# Put your username and password
username = 'login'
password = 'password'
data = 'login=' + username + '&apiKey=' + password
url = "https://app.siliconexpert.com/ProductAPI/search/authenticateUser"
r = s.post(url, data=data, headers=headers)
 
# default is fmt=json
data = 'fmt=xml&partNumber=[{"partNumber":"bav99wt"}]'
url = "https://app.siliconexpert.com/ProductAPI/search/listPartSearch"
r = s.post(url, data=data, headers=headers)
df = pd.DataFrame(r)
print(df)

This returned a series of rows which I then converted into a single text field using PQ, then parsed into XML & generated the data I needed.

I then edited the code to insert Power Query parameters for the username, password and part number, then converted this to a function and applied it against my data set of part numbers as an invoked function.

Thanks for your help. I hope this helps someone else in future!

Jeff

[v-rzhou-msft]

Hi @JeffRobson 

Remove the credential in the Data Source Setting and connect to api again by this code.

I think you can try to authenicate by access token. 

For reference:

Using a REST API as a data source

Using a REST API as a data source - POST Method Only

Video: Get access token and then connect to Rest API in Power Query

https://www.youtube.com/watch?v=N8qYRSqRz84

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[JeffRobson]

Thanks for your suggestions, Rico.

Unfortunately, it seems the API I'm trying to connect to doesn't support access tokens. It seems to require the cookies created during the authorization process for each API call.

For example, the cURL code for a part number search is:

# 1) Creating a new directory (if not exists) to save cookies.
mkdir /tmp/siliconexpert
 
# 2) Creating a new file (if not exists) to save cookies.
touch /tmp/siliconexpert/cookies
 
# 3) Authentication (if not authenticated or session expired).
#   -d (Send specified data in POST request).
#   -X (The request method to use).
#   login (Your API username).
#   apiKey (Your API password).
curl -c /tmp/siliconexpert/cookies -d "login=my_username&apiKey=my_password" -X POST https://app.siliconexpert.com/ProductAPI/search/authenticateUser
 
# 4) Calling the API.
curl -b /tmp/siliconexpert/cookies -d "partNumber=[{\"partNumber\":\"bav99wt\"},{\"partNumber\":\"bav99\"}]&mode=beginwith&pageNumber=1&fmt=xml" -X POST https://app.siliconexpert.com/ProductAPI/search/listPartSearch

https://www.siliconexpert.com/apidocs/#list-part-search

Unfortunately, I'm not sure how to capture the cookies then pass them to the subsequent calls.

Is there a way to do this?

I attempted the R solution to this detailed at https://community.powerbi.com/t5/Desktop/How-to-extract-http-response-header/m-p/452992 but there's something still not quite right as it still says I'm not authenticated & the tokens seem to fail.

Do you have any other ideas?

Thanks