Performance Issues with Custom HubSpot Connector in Power BI (Function.InvokeAfter)

[SparkByte_202]

Dear Community,

I've developed a custom connector to import data from HubSpot via its API into Power BI. Given HubSpot's API limitation of 100 results per call, my solution involves making approximately 50 recursive requests using Web.Contents to fetch a substantial dataset. This process typically takes about 30 seconds to execute and construct a table on my system.

However, I encountered an issue where some requests failed due to HubSpot's 10 requests/second limit. I observed these failures and subsequent automatic retries in my HubSpot App's developer console. To address this, I implemented a workaround by encapsulating my Web.Contents call within a Function.InvokeAfter, introducing a 0.1-second delay between requests. This adjustment not only ensured all requests were successful but also reduced the total execution time to approximately 15 seconds.

Window to filter column content

However, I encountered an issue where some requests failed due to HubSpot's 10 requests/second limit. I observed these failures and subsequent automatic retries in my HubSpot App's developer console. To address this, I implemented a workaround by encapsulating my Web.Contents call within a Function.InvokeAfter, introducing a 0.1-second delay between requests. This adjustment not only ensured all requests were successful but also reduced the total execution time to approximately 15 seconds.

Unexpectedly, this modification has led to significant performance degradation in Power BI. After integrating the table and attempting transformations, any operation—such as applying a simple text filter—now experiences a delay. For instance, when attempting to "Load more" options for a filter, it takes about 15 seconds for Power BI to display the results, during which it indicates "Waiting for Hubspot_Connector". Prior to using Function.InvokeAfter, these operations were nearly instantaneous.

Moreover, operations like Table.RowCount introduce an unwarranted delay, even though no additional API calls are made after the initial data retrieval. This leads me to wonder why the InvokeAfter delay affects subsequent data operations within Power BI.

Has anyone encountered similar issues or can provide insight into why these performance bottlenecks are occurring despite the absence of further API calls? Any advice or suggestions would be greatly appreciated.

Thank you for your time and assistance.

Best regards,
SparkByte_202

[QueryWhiz]

Hi @SparkByte_202 As a workaround, maybe you can use a 3rd party connector, which pulls data directly from the Hubspot API, it can save you a lot of time compared to other options. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Hubspot connector in the data sources list:

After that, just grant access to your Hubspot account using your credentials, then on preview and destination page you will see a preview of your Hubspot fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[Anonymous]

Hi @SparkByte_202 

You can consider to use Table.Buffer() to buffer a table in memory, you can refer to the following link about it .

Table.Buffer - PowerQuery M | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SparkByte_202]

Hi @Anonymous 

Unfortunately using Table.Buffer() has no effect on the performance for me.
I tried using it at various location in my code and even just as an extra step in the transformation window in Power Bi, but to no avail. As soon as InvokeAfter is in the code, a delay is added to all subsequent transformations as it seems.

I've coded a little test function (adding a column to the source table), which reliably comes to the same result if run multiple times:

1. The code without InvokeAfter takes ~0.5 sec to add a new Column.
2. With InvokeAfter, the addColumn function takes ~ 7 sec.
3. Both variants are equally performant when adding Table.Buffer() or Table.StopFolding().

Anyway, thank you for your help!

Best regards,
SparkByte_202