Power BI Service refresh timeout and 429 Too Many Requests on Teamleader Focus

[07m5]

Platform:Teamleader Focus
Tool: Power BI Service (automatic refresh) + Power BI Desktop

Automatic refresh of the semantic model “AucoteamTickets” fails regularly with two different errors:

1. Timeout after 10–45 minutes with error code Microsoft.Data.Mashup.ErrorCode = 10122 - underlying connection closed on OData request.
2. HTTP 429 Too Many Requests during manual optimization attempts.

Manual refresh in Power BI Desktop works, but takes very long. Automatic refresh in Power BI Service fails consistently.

Without the TicketMessagesAll table the report refreshes without any problems. We cannot remove this table however, as it is the only source that contains the full ticket description/body text. The ticket detail view in our report depends entirely on this table.

Data volumes:

• Tickets: approximately 140 (filtered to one customer ID).
• TicketMessages: approximately 140 currently, expected approximately 600 by end of 2026.
• Contacts: unknown total, filtered to 3 columns.
• Users: approximately 15.
• Companies: small
• TicketStatuses: small

Already applied optimizations:

• Moved all filters into OData URL ($filter, $select, $orderby, $top).
• Disabled parallel table loading in Power BI.
• Added Accept-Encoding: gzip, deflate header.
• Removed redundant Table.SelectColumns steps.
• Set $top=2000 to avoid pagination.

Current M-Queries:

Teamleader V2 Tickets@tlr

let
    KundenID = "739295e2-7443-07b1-aa70-bbba438579f5",
    URL = "https://bridge-online.invantive.com/acme-teamleader-focus/odata4/" &
          "Teamleader.V2.Tickets@tlr" &
          "?$filter=customer_id eq '" & KundenID & "'" &
          " or participant_customer_id eq '" & KundenID & "'" &
          "&$select=subject,id,assignee_id,status_id,customer_id," &
          "participant_customer_id,closed_at,created_at,reference,project_id",
    Quelle = OData.Feed(URL, null, [
        Implementation="2.0",
        Headers=[#"Accept-Encoding"="gzip, deflate"]
    ])
in
    Quelle

Teamleader V2 TicketStatuses@tlr:

let
    URL = "https://bridge-online.invantive.com/acme-teamleader-focus/odata4/" &
          "Teamleader.V2.TicketStatuses@tlr",
    Quelle = OData.Feed(URL, null, [
        Implementation="2.0",
        Headers=[#"Accept-Encoding"="gzip, deflate"]
    ])
in
    Quelle

Teamleader V2 Companies@tlr:

let
    URL = "https://bridge-online.invantive.com/acne-teamleader-focus/odata4/" &
          "Teamleader.V2.Companies@tlr" &
          "?$select=name,id",
    Quelle = OData.Feed(URL, null, [
        Implementation="2.0",
        Headers=[#"Accept-Encoding"="gzip, deflate"]
    ])
in
    Quelle

Teamleader V2 Users@tlr:

let
    URL = "https://bridge-online.invantive.com/acme-teamleader-focus/odata4/" &
          "Teamleader.V2.Users@tlr",
    Quelle = OData.Feed(URL, null, [
        Implementation="2.0",
        Headers=[#"Accept-Encoding"="gzip, deflate"]
    ])
in
    Quelle

Teamleader V2 Contacts@tlr:

let
    URL = "https://bridge-online.invantive.com/acme-teamleader-focus/odata4/" &
          "Teamleader.V2.Contacts@tlr" &
          "?$select=first_name,last_name,id",
    Quelle = OData.Feed(URL, null, [
        Implementation="2.0",
        Headers=[#"Accept-Encoding"="gzip, deflate"]
    ])
in
    Quelle

Teamleader V2Flat TicketMessagesAll@tlr:

let
    KundenID = "739295e2-7443-07b1-aa70-bbba438579f5",
    FilterParam =
        "(ticket_customer_id eq '" & KundenID & "'" &
        " or ticket_participant_customer_id eq '" & KundenID & "')" &
        " and created_at ge 2026-01-01T00:00:00Z" &
        " and (type eq 'customer' or type eq 'outbound' or type eq 'third_party')",
    URL = "https://bridge-online.invantive.com/acme-teamleader-focus/odata4/" &
          "Teamleader.V2Flat.TicketMessagesAll@tlr" &
          "?$filter=" & Uri.EscapeDataString(FilterParam) &
          "&$select=message_id,ticket_id,body,type,created_at,sent_by_id" &
          "&$orderby=created_at asc" &
          "&$top=2000",
    Quelle = OData.Feed(URL, null, [
        Implementation="2.0",
        MaxUriLength=32000,
        Headers=[#"Accept-Encoding"="gzip, deflate"]
    ]),
    #"HTML bereinigt" = Table.TransformColumns(Quelle, {{"body", each
        let t = _ in
        Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(
        Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(
        Text.Replace(Text.Replace(Text.Replace(Text.Replace(
            t, "<br />", " "), "<br/>", " "),
            "&uuml;", "ü"), "&Uuml;", "Ü"),
            "&auml;", "ä"), "&Auml;", "Ä"),
            "&ouml;", "ö"), "&Ouml;", "Ö"),
            "&szlig;", "ß"), "&amp;", "&"),
            "&lt;", "<"), "&gt;", ">"),
            "&eacute;", "é"), "&nbsp;", " ")
    }})
in
    #"HTML bereinigt"

Questions:

1. Is there a recommended approach for Teamleader Focus to avoid 429 errors and timeouts during Power BI Service refresh when using TicketMessagesAll?
2. Are there cached or incremental variants of TicketMessagesAll available, similar to incremental sync tables on other platforms?
3. Is the body/description field of a ticket accessible through any other table or endpoint that is less expensive in terms of API calls?

[forums]

The approach is different from the one typically used. It is recommended to consult the person within your company which had the one hour free training session for the regular approach.

Regarding 429 errors, please consult:

Fair Use Daily Limits for a Better User Experience

Go to Dutch version Summary Invantive is introducing “Fair Use Daily Limits” to guarantee an optimal user experience on Invantive Bridge Online. These limits are centred around four key metrics: volume of data exchanged, number of requests made, total time taken for requests, and the number of rows retrieved from a table. The limits are categorised into fresh data usage and overall data usage, which includes cached data. The rollout is planned for 1 October 2023, but this timeline may be revise…

to determine on what rate limiter is being exceeded.

Note that the table function TicketMessagesByTicketId is executed once for each and every ticket with filtering. For optimization it is in general not recommended to construct URLs in code unless the logic is fully understood of the UniversalSQL Engine and OData, but to use the advantages of “query folding”. OR-optimization is in general not available on database platforms.

There is no known efficient alternative to retrieve the body of all messages offered by Teamleader APIs.

[forums]

This question was automatically closed after at least 1 week of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.