How to access an REST API with a refreshable token | Translate Postman to Power Query

[Gjakova]

Hi there,

I have the following question: I need to get access to an API and altough I can get it to work in Postman, I can't seem to figure it out how to run it in Power BI.

I have the following info:

API-key: 1938adklw-2020etbw-3abca-9add0-433435 (it is a fictive key...)

In Postman I put a POST request with the following URL (also fictive):

https://worksauce.matrix2.de/auth/login/matrix2pbi/apiKey?

And in my headers I put:
Key = apiKey

Value = 1938adklw-2020etbw-3abca-9add0-433435

When I press Send in Postman, I get an access token from Postman, something like:

eyJhbGciOiJSUsdjklfklsdjlkfjioewsjfjsiovjioerj908834u89jf908wej98dsja98f4ejw984jf893jiadsguiods9f8j

Once I copy the token I go to another tab and create a GET request.

At Authorization I select the Type = Bearer Token and past the token above into it: eyJhbGciOiJSUsdjklfklsdjlkfjioewsjfjsiovjioerj908834u89jf908wej98dsja98f4ejw984jf893jiadsguiods9f8j

The URL is a GET request and looks like: https://worksauce.matrix2.de/list/clients

Does someone know how to translate this to Power Query?

Many thanks in advance!

[Gjakova · Accepted solution]

Hi all, just to give an update, I managed to get it to work with the following script:

let
  GetJson = Web.Contents(
                       "https://company.abcde.com/",
                         [
                           RelativePath=tokenURL,
                           Headers = [#"Accept"="application/json", #"Content-Type"="application/json"],
           Content = Text.ToBinary("token=xxxxxxxx&iat=xxxxx*&resource=xxxxx&grant_type=client_credentials")
       ]
   ),
  FormatAsJson = Json.Document(GetJson),
  token = FormatAsJson[token],
  // add token in the URL:
  Source = Json.Document(
      Web.Contents(
        "https://company.abcde.com/", 
            [
          RelativePath=ListURL,
          Headers=[Authorization="Bearer "&token]
            ]
          )
        ),
  Navigation = Source[items],
  #"Converted to table" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
  #"Converted to table"

The thing betweens " in the Content = part is not really important according to another topic of mine: https://community.powerbi.com/t5/Power-Query/Refreshable-token-API-Credential-Errors-when-trying-to-...

[negi007]

@Gjakova similar solution is available at below link. i also used the same for my data connection

https://community.powerbi.com/t5/Power-Query/Pull-data-from-a-REST-API-Authentication/td-p/246006