Help accessing a specific API

[andrew-waring]

Hi All, I'm relatively new to power query and i've been bashing my head against this problem for a while. I have literally no idea how to access the data on a websites API. I have the client secret and client ID for the api. 

the API documentation can be found here

Uniclass API | NBS (thenbs.com).

There is a Get function defined and a json from an export button at the bottom. 

I've tried all manner of webquerys an json stuff and have sorted it for airtable which seems to be a striaght URL query but this is differnt and to be truthful, i have no idea what i'm doing.

Appreciate this is a big ask but could someone donate some of their time to explain to me like im 5 years old how this works?

[Krazbo]

Hi @andrew-waring did you get a working solution to this?

[andrew-waring]

Hi,

Thanks so much for your response. I've followed your instructions as best I can but am getting the error 405 method not allowed for the GetJson = Web.Contents bit. not sure what you mean by scopes. I'm not familiar with API language generally. 

let
 // Concatenates the Consumer Key & Consumer Secret and converts to base64
 authKey = "Basic " & Binary.ToText(Text.ToBinary("xxxxxx:xxxxxx"),0),
 url = "https://toolkit-api.thenbs.com:443/",
 // Uses the Twitter POST oauth2/token method to obtain a bearer token
 GetJson = Web.Contents(url,
     [
         Headers = [#"Authorization"=authKey,
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
                         Content = Text.ToBinary("grant_type=client_credentials&scope=Get") 
    
     ]
 ),
 FormatAsJson = Json.Document(GetJson),
 // Gets token from the Json response
 AccessToken = FormatAsJson[access_token],
 AccessTokenHeader = "bearer " & AccessToken,
 // Uses the Twitter GET search/tweets method using the bearer token from the previous POST oauth2/token method
 GetJsonQuery = Web.Contents("https://schema.getpostman.com/json/collection/v2.1.0/collection.json",
     [
         Headers = [#"Authorization"=AccessTokenHeader]
     ]
 ),
 FormatAsJsonQuery = Json.Document(GetJsonQuery),
 NavigateToStatuses = FormatAsJsonQuery[statuses],
 TableFromList = Table.FromList(NavigateToStatuses, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    TableFromList

I took a punt at the scope being the Get function? may be way off. 
As you can probably tell, i'm completely lost but, further forward than i was!
 

[Querynalyst]

Hi,

405 error will be specified in your API documentation (usually this error occurs when user uses GET method instead of POST or PUT for example).

Regarding scopes, you will know that you need scopes in your endpoint API documentation, if nothing is specified then you don't need any. You can first try to not put any scope, only keep:

Content = Text.ToBinary("grant_type=client_credentials")

You don't need to precise that you use GET method in the code, if you precise a "Content" in your script then Power query understands that you are using a POST method. If you don't use "Content" then it automatically performs a GET call.

I hope this will help, good luck.

[Querynalyst]

Hello,

I already did the same thing than you and I found this existing topic:

https://community.powerbi.com/t5/Power-Query/Pull-data-from-a-REST-API-Authentication/m-p/247252#M12... 

In the solution, you can see this mentionned URL: https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/ 

I did those steps and it worked for me:

1/ Create a blank query in Power Query

2/ Click on the Advanced Editor

3/ Copy Paste the M script provided in the link above

4/ Replace ConsumerKey & ConsumerSecret with your client ID and your client secret

5/ Replace url (line 14) with your oauth2 authentication URL (in your API documentation)

6/ In my case I needed to add scopes, if it your case too, line 20:

Content =Text.ToBinary("grant_type=client_credentials&scope=<API Scope(s) separated by spaces>"

7/ Line 28: replace the Twitter endpoint by yours (in your API documentation)

I hope this will help