PowerQuery refresh timeout (integration with Hubspot through API)

[Sergii24]

Hi all, I try to load data from Hubspot CRM system using API calls to PowerBI dataflow. I use Private App in Hubspot - Private apps (hubspot.com) and authentificate through a token.

As described here HubSpot APIs | Usage guidelines, API call limitation is 100 records per 10 seconds. Therefore I add "?limit=100" to my query (if I don't do so, I get 10 items, if I use any number more than 100 I still get 100 records).

My table contains 1432 rows, therefore I use a function getPage() to loop over next 100 items until I reach the end of the table. It works perfectly fine in PowerBI desktop (I'm not even sure if there is the mentioned 10 seconds/100 rows limit, as it is loading to PowerBI desktop in seconds).

However, when I copy-paste the same code to dataflow in PowerBI service and lauch a refresh, I get 2 hours timeout error. Even if I suppose that it takes 10 seconds to call every 100 rows, it should take approx. 3 minutes (add other simple data tranformations so let it be 10min, but definitely not 2 hours).

Is there anything I do wrong? Any suggesiton on how to optimize its performance?
Thank you for your time and support! 

Deals query code (the main query to retreieve the data): 

let
    // Define the URL elements for API call (token is defined in getPage()
    queryParameters = "?limit=100",
    
    // Fetch the first page of data
    firstPage = getPage(queryParameters),
    pageData = firstPage[Data],
    nextPageToken = firstPage[NextPage],

    // Loop through each page and retrieve data until no more data is returned
    allData = List.Generate(
        () => [Data = pageData, NextPage = nextPageToken],
        each List.Count([Data]) > 0,
        each
            let
                nextUrl = if [NextPage] <> null then queryParameters & "&after=" & Text.From([NextPage]) else null,
                nextPageData = if nextUrl <> null then getPage(nextUrl) else [Data = {}, NextPage = null]
            in
                [Data = nextPageData[Data], NextPage = nextPageData[NextPage]],
        each [Data]
    ),

    // Combine all pages into a single list
    combinedData = List.Combine(allData),

    // Convert the list of records to a table
    table = Table.FromList(combinedData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(table, "Column1", {"id", "properties", "archived"}, {"id", "properties", "archived"}),
    #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"amount", "closedate", "createdate", "dealname", "dealstage", "hs_lastmodifieddate", "hs_object_id", "pipeline"}, {"amount", "closedate", "createdate", "dealname", "dealstage", "hs_lastmodifieddate", "hs_object_id", "pipeline"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded properties", {{"createdate", type datetimezone}, {"hs_lastmodifieddate", type datetimezone}, {"closedate", type datetimezone}}, "en-US"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"createdate", type date}, {"hs_lastmodifieddate", type date}, {"closedate", type date}, {"id", Int64.Type}, {"dealstage", Int64.Type}, {"hs_object_id", Int64.Type}, {"pipeline", Int64.Type}, {"dealname", type text}, {"archived", type logical}, {"amount", type number}})
in
    #"Changed Type"

 
getPage() function:

let
    // Function to fetch data for a specific page
    getPage = (queryParameters as text) as record =>
        let
            headers = [
                Authorization = "Bearer " & Token,
                Query = queryParameters
            ],
            pageResponse = Web.Contents("https://api.hubapi.com/crm/v3/objects/deals", [Headers=headers]),
            pageJson = Json.Document(pageResponse),
            pageData = pageJson[results],
            nextPage = try pageJson[paging][next][after] otherwise null
        in
            [Data = pageData, NextPage = nextPage]
in
    getPage

 That's how getPage() looping looks like:

[ReportMaster]

Hi @Sergii24 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 the 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 @Sergii24 ,

Try using the base URL like this and introducing relativePath:

= (queryParameters as text) as record =>
        let
			baseuri = "https://api.hubapi.com",
			relativePath = "/crm/v3/objects/deals",
            headers = [
                Authorization = "Bearer " & Token,
                Query = queryParameters
            ],
            pageResponse = Web.Contents(baseuri, [RelativePath=relativePath, Headers=headers]),
            pageJson = Json.Document(pageResponse),
            pageData = pageJson[results],
            nextPage = try pageJson[paging][next][after] otherwise null
        in
            [Data = pageData, NextPage = nextPage]

Similar thread, hope this helps:

Solved: Hubspot API POST request with relative path - Microsoft Fabric Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Sergii24]

Hi @Anonymous , thanks for the suggestion!

I suppose that base URL must be a valid API url, (as suggested here Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI (crossjoin.co.uk) ), in addition, I can't create loop to create a necessary URL as I'm getting in a trouble with "dynamic source" (so I can't save dataflow)...