Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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.
Hi @v-xinruzhu-msft
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:
Anyway, thank you for your help!
Best regards,
SparkByte_202
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.