Live Agent (LA) - API connection to Power BI

Hi LK0913,

Yes, that can happen with this pattern.

The custom function approach works, but there is an important performance tradeoff: Power Query is effectively making one API request for every Ticket ID in your ID's table.

So if you have 500 Ticket IDs, that can mean roughly 500 separate calls to:

/api/v3/tickets/{id}/history

and potentially more during preview/refresh because Power Query can evaluate referenced queries multiple times.

That would explain why it works functionally but becomes very slow when loading the full dataset.

A few things I would try:

• Filter the ID's query as early as possible before invoking fnTicketHistory.
  If you only need a subset of tickets, reduce that list first and only then call the API.
• Make sure the staging ID's query itself is not being loaded into the model if you only use it as an intermediate query. You can right-click it and disable Enable load.
• Test with perhaps 10 or 20 Ticket IDs first. If that loads quickly but 500+ becomes extremely slow, that confirms the bottleneck is the number of API calls rather than the transformation logic.
• Use Query Diagnostics in Power Query to see how many Web.Contents requests are actually being generated. Microsoft documents Query Diagnostics specifically for investigating slowdowns and background activity during query evaluation.

I would also avoid adding Table.Buffer() immediately. Microsoft notes that buffering can sometimes make queries slower rather than faster, so I would first prove that repeated evaluation is actually the issue before introducing it.

For the sorting/filtering part, I would do that on the ID's table before the custom function is invoked, for example:

FilteredIDs =
    Table.SelectRows(
        #"Previous Step",
        each [ID] <> null
    ),

SortedIDs =
    Table.Sort(
        FilteredIDs,
        {{"ID", Order.Ascending}}
    ),

WithHistory =
    Table.AddColumn(
        SortedIDs,
        "TicketHistory",
        each fnTicketHistory(Text.From([ID]))
    )

That way Power Query only calls LiveAgent after you have already reduced and ordered the input list.

If you ultimately need the history for a very large number of tickets on every refresh, I would probably reconsider the architecture rather than calling the per-ticket endpoint hundreds or thousands of times from Power Query. For example, I would check whether LiveAgent exposes a bulk/history endpoint or another API route that lets you retrieve more records per request and then join them back to Ticket ID in Power Query.

LiveAgent’s API documentation does expose pagination and API limits, so reducing the number of round trips will usually matter much more than tweaking the DAX/model side.