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 single use token (Bearer). That Bearer is used in a separate call to access the data.

 

Now, I managed to get the token in DAX using:
= let
    body = "      {""client_id"":""XXXXX"",""client_secret"":""ZZZZZ""}",
     Data= Web.Contents("URL_/auth?",[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]),
DataRecord = Json.Document(Data),
Bearer=DataRecord[data][token]
in
Bearer

This successfully returns the single-use token (Bearer) and upon every "refresh" brings a new one.

 

Then I'm stuk in passing that Bearer to the next API call to retrieve the data.

 

Any suggestions??

Thanks in advance!

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!

5 Replies

  • 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? 

         

  • prodoreda's avatar
    prodoreda
    Frequent Visitor
    3 years ago

    Hi Kaido!

    I got the same problem and what worked for me was to create 2 separate keys in personio: 1 for employees and 1 for Time-offs.
    That way when calling the API it opens 2 separate end points with 2 different keys and has been working ever since.

    Other thing is that I don't refresh all the queries at the same time but 1 by 1

  • PhilippM's avatar
    PhilippM
    Advocate I
    2 years ago

    Hey there,

    thank you guys for your effort. I have tried adapting your solution, but to no success. Is this still working for you or do you happen to have any updated solutions?

     

    Thanks in advance!