Creating and triggering a Get Data url dynamically for an API request

[MattTrollope]

Hi

I'm bringing data from the TMetric time recording software into BI using a custom API connector I've created. One of the API requests returns data for users' time entry data when their ID is sent as a query parameter. If the parameter is omitted it returns data for the currently connected user, but there isn't a way of returning the time entries for all users within one request, so I have an API request for each user.

I want to be able to return this data for new users when they start, but until they are created in TMetric and have a user ID assigned I'm not able to make the Get Data request for them. The new user's ID is available from other requests to retrieve, so is available to utilise. Is it somehow possible to dynamically create the url for the Get Data request, so that I don't have to create another API request manually every time a new user starts?

Many thanks for any suggestions/ideas.

[MattTrollope]

Thank you for your reply, I will proceed in this way.

I'm thinking my main issue will be automatically invoking the Get Data process for the new API requests when they exist, but am hoping this might be covererd in the subsequent reply from v-hashadapu.

Matt Trollope

[v-hashadapu]

Hi @MattTrollope , Thank you for reaching out to the Microsoft Community Forum.

The key is that you don’t need to invoke new API requests manually each time a new user appears. If your query starts by pulling the up-to-date list of users (including any new ones) and then dynamically loops through their IDs during each refresh, Power BI will automatically fetch data for everyone currently in that list. So as soon as a new user exists in TMetric and appears in that source, their time entries will be included in the next refresh, no extra trigger or manual setup needed.

[v-hashadapu]

Hi @MattTrollope , Thank you for reaching out to the Microsoft Fabric Community Forum.

@GeraldGEmerick  is right, Instead of manually creating a new API request each time someone joins, you can keep a list or table of user IDs and have your connector loop through that list. The function would build the API URL dynamically for each user, fetch their time entry data and combine all the results into one dataset. That way, whenever a new user is added to the list, the process automatically includes them without any extra setup.

Function.Invoke - PowerQuery M | Microsoft Learn

Using custom functions in Power Query - Power Query | Microsoft Learn

Understanding Power Query M functions - PowerQuery M | Microsoft Learn

Using the wait-retry pattern in Power Query connectors - Power Query | Microsoft Learn

Power Query M function reference - PowerQuery M | Microsoft Learn

Connector Extensibility in Power BI - Power BI | Microsoft Learn

Thank you @GeraldGEmerick  for your valuable response and continued contribution in the community.

[MattTrollope]

Thank you for your assistance.

As mentioned in my reply to @GeraldGEmerick I'm hoping the automatic invokation of the Get Data process for new API requests might be covererd in your subsequent reply. Also thank you for the links.

[GeraldGEmerick · Accepted solution]

@MattTrollope You could put the list of user ids in a table and then iterate over that list with a function that passes in the user id and builds the URL dynamically.