Power BI and Personio API: Dynamic single-use token

[prodoreda]

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!

[PhilippM]

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!

[prodoreda]

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

[Anonymous]

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 · Accepted solution]

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]

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?