HubSpot API connection with Power BI: Failing properties

[Alina_w]

Hi all,

we built an API connection from our HubSpot account to Power BI for reporting purposes. To do so, we identified the relevant properties and included them in the API key. Somehow, for some properties this works perfectly and for others it does not transfer any data at all. We cannot find a clear pattern that explains why some proerties fail and some don't. The properties are mostly customised and no HubSpot standard properties. 

Here is a screenshot of our Power BI Dataflow:

„Screenshot of our Power BI Dataflow“

Is anyone experienced with this kind of problem and can help us out? 

Thanks for your help 🙂

[SprinkleData]

Hi @Alina_w ,

Looking at your problem, I recommend you use a data pipeline solution that can integrate data from Hubspot and push it to Power BI for reporting purpose. At Sprinkle, we have built a platform which connects easily with Hubspot to integrate data and Power BI for reporting purpose. 

Regards

Sam

[Derek_Gervais]

Hey @Alina_w ,

The HubSpot API will only return a property if a contact record has a value for that property; otherwise, the propery won't be included in the response. For example, if you're making the following request: https://api.hubapi.com/deals/v1/deal/paged?hapikey=xxx&properties=name&properties=amount&properties=..., we'd expect to see the name, amount, and notes property for all deals which have a value for those properties. 

Is it possible that you have deal records without values for certain properties? Otherwise, can you give me your Hub ID and some example deals which aren't passing all the proper information?

[Alina_w]

Hi @Derek_Gervais ,

Thanks for your reply to my post (https://community.hubspot.com/t5/APIs-Integrations/HubSpot-API-connection-with-Power-BI-Failing-prop...)!

The problem is that some properties are not included in the response at all. This means that for ALL deals the information for these properties is not transmitted. 

I've sent you a private message with our HUbSpot ID. 

[Derek_Gervais]

Hey @Alina_w ,

Thanks for sending that info along! Upon closer inspection, I'm able to see the `utm_source_first` property populated for a number of deals returned from that endpoint. There doesn't appear to be any deal with a value for `utm_source_first` returned from this endpoint for the first ~2700 results. After this point, deals with a value for that property appear much more regularly. Similarly, the `sql` property you mentioned is populated for even fewer deals (~300 total), and the first result doesn't appear for the first ~1000 deals. 

Is it possible that you're not paging through enough results to see records that have a value for these properties? Or otherwise, do you have the ID of an example deal that does have a value for one of these properties in app, but does not when pulled via the API?

[Alina_w]

Hi @Derek_Gervais,

thanks for taking a closer look on this. You are absolutely right. The majority of deals does not hold a value for both properties because we have only been using them for approx. half a year. Nevertheless, the problem is that not only do we not have any values for any deal for these properties in Power BI, but they are not even displayed there. It seems that for these two the API connection doesn't work.

Here is a list of deals that hold values for both properties:

• 873885216

• 873859634

• 710421826

• 685283296

• 697002935

Let me know if you need more information!

[Derek_Gervais]

Hey @Alina_w ,

Thanks for listing some more example deals. I wrote a simple script that pulls deals using the "Get all deals" endpoint & looks for specific deal IDs. Using this script, I was able to find all of the deals you have listed here. I'm not 100% clear on why they might be missing from Power BI, but I can confirm that they're appearing in the results of the HubSpot "Get all deals" endpoint.

Unfortunately I'm not familiar with Power BI specifically. Is it possible to log the full results from the query you included in your screenshot? I'm wondering if the query might be timing out before pulling all of the deals, or if it's somehow missing certain results?

Apologies if it sounds like I'm grasping at straws here; I'm just trying to identify where exactly the break in functionality is occuring. Let me know if you have any additional info or if there's another avenue I can help you explore here!