Forum Discussion

prodoreda's avatar
prodoreda
Frequent Visitor
4 years ago
Solved

Power BI and Personio API: Dynamic single-use token

Hey all!  First post here 🙂 I'm trying to connect our Personio account API as a data source for our Power BI. Context is, Personio API requires a CLIENT_ID and a SECRET whit which generates a sin...
Need Help
Tutorial Requests
  • prodoreda's avatar
    prodoreda
    4 years ago

    Hey, thanks for your reply Anonymous !!

    I was able to spare some time during the weekend and four a way to make it work:
    let
    Source = let
    body = "{""client_id"":""XXXXXXX"",""client_secret"":""ZZZZZZZ""}",
    Data= Web.Contents("https://api.URL.com/auth?",[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]),
    DataRecord = Json.Document(Data),
    Bearer=DataRecord[data][token],
    bearerBody = Web.Contents( "https://api.URL.de/v1/company?",[Headers=[#"Accept"="application/json",#"Authorization"="Bearer "&Bearer]]),
    employees = Json.Document(bearerBody)

    in
    employees

    Basically I captured the response in a variable, then replicated the first call, changing the headers to include the "Authorization" and passing the variable to it.

    So far is still working with every refresh.

    I´ll check also your solution to see which one is faster 🙂

    Thanks!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi prodoreda ,

 

Firstly, try to get data by Web connector and you need to add Bear token in HTTP request header parameters(Authorization) in Advanced.

Then open Advanced Editor in Power Query Editor and replace the token by your token Query.

Result is as below. Each time you refresh the report will get the new token from token query.

For more details, you may refer to this video  to learn more details.

 

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.

 

  • prodoreda's avatar
    prodoreda
    Frequent Visitor
    4 years ago

    Hey, thanks for your reply Anonymous !!

    I was able to spare some time during the weekend and four a way to make it work:
    let
    Source = let
    body = "{""client_id"":""XXXXXXX"",""client_secret"":""ZZZZZZZ""}",
    Data= Web.Contents("https://api.URL.com/auth?",[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]),
    DataRecord = Json.Document(Data),
    Bearer=DataRecord[data][token],
    bearerBody = Web.Contents( "https://api.URL.de/v1/company?",[Headers=[#"Accept"="application/json",#"Authorization"="Bearer "&Bearer]]),
    employees = Json.Document(bearerBody)

    in
    employees

    Basically I captured the response in a variable, then replicated the first call, changing the headers to include the "Authorization" and passing the variable to it.

    So far is still working with every refresh.

    I´ll check also your solution to see which one is faster 🙂

    Thanks!

    • KaidoPowerBI's avatar
      KaidoPowerBI
      Regular Visitor
      3 years ago

      I was looking for an option to access Personio via API and this one worked, however for a short time.
      I was able to get data for several times from employees API with this approach and then investigated also grabbing data from time-offs API. Unfortunately, soon after I have not been able to get anyting and get an error that "we couldn't autheticate with the credentials" (I choose Anonymous, on the level of company). Strange thing is that the "Bearer" query to "auth" givesme the token, but somehow this now fails (though worked for a while).

      Here is full Query script I have used. Any help is appreciated. It could be, that Personio API is simply too unstable.

       

      let
      Source = let 
      body = "{""client_id"":""xxxxxxxxxxxxxxxxxxxxxxx"",""client_secret"":""yyyyyyyyyyyyyyyyyyyyy""}",
      Data= Web.Contents("https://api.personio.de/v1/auth?",[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]),
      DataRecord = Json.Document(Data),
      Bearer=DataRecord[data][token],
      bearerBody = Web.Contents( "https://api.personio.de/v1/company/employees?limit=200",[Headers=[#"Accept"="application/json",#"Authorization"="Bearer "&Bearer]]),
      employees = Json.Document(bearerBody)

      in employees
      in Source



      Do you have any similar experience?