How to query multiple endpoints in Clockify web API

[yirdaki]

Hi!

I am new at Power BI. Been lurking the forums for a while. Now I've come to a point where I don't know what to do, so I thought I'd ask people who know what they are doing.

I am using Clockify's API ( https://clockify.me/developers-api#operation--v1-workspaces--workspaceId--user--userId--time-entries... ) to make separate queries for the endpoints Workspaces, Projects, Users, and Clients. I have all the data except for the actual crucial data, which is the Time Entries and maybe the associated Tasks as well.

I need to be able to look at number of hours per user per project per month.

My problem is that the API is in this format "/workspaces/{workspaceId}/user/{userId}/time-entries".

The WorkspaceId is fine because we are only using one Workspace for the whole company anyway. The problem is that we have hundreds of employees, and I do not think that I should be expected to manually enter hundreds of separate queries for each UserId.

I have tried to enter the UserId's as a list, and then put that straight into the URL, but then Power BI complains that it cannot use & to combine a text with a list, which is understandable.

I don't know what else to do. Can anyone please give me some pointers? 🙂

[lbendlin · Accepted solution]

Put the userids in a table and then in Power Query add a column that calls a custom function to retrieve that user's data from Clockify.  This will give you a binary cell content that you can then transform as needed (probably parse the JSON, or similar).  If you only need certain fields for each user then you can also push the parsing to the custom function.

[yirdaki]

That worked!

Thank you 🙏