Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Sergii24
Super User
Super User

PowerQuery refresh timeout (integration with Hubspot through API)

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:

Sergii24_0-1720433045541.png

 

 

3 REPLIES 3
ReportMaster
Post Patron
Post Patron

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:

 

HUBSPOT-1.png

 

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:

 

HUBSPOT-2.png

 

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. 

 

SELECT_DESTINATION_NEW.png

 

Anonymous
Not applicable

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

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)...

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors