HubSpot engagements in Power Bi Dashboard

[KevanGallagher]

Hi! 

So with the help of this community, I have been able to connect Hubspot and Power Bi and implement a script for a pagination. I'm already stoked about how far I got but I have run into two issues that I can't seem to figure out. 

1. I'm getting duplicate values on almost every engagement that I pull in. 

2. I'm getting the following error on some of my rows:

Expression.Error: The field 'scheduledTasks' of the record wasn't found.
Details:
engagement=[Record]
associations=[Record]
attachments=[List]

So I know why the error is popping up but I don't know how to solve it. 

My code is:

let
Pagination = List.Skip(List.Generate( () => [IsMore = null, Last_Key = 0, Counter = 0], // Start Value
each [IsMore] <> false,// Whilst this is true, keep going
each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/engagements/v1/engagements/recent/modified?hapikey=XXXX&count=100&since=12096... retrieve results per call
Last_Key = try [WebCall][#"offset"] otherwise 0,
IsMore = if [Counter] < 1 then null else [WebCall][#"hasMore"],
Counter = [Counter]+1,
Table = Table.FromRecords(WebCall[results])
]
,each [Table] // selector
) ,1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"engagement", "associations", "metadata"}, {"Column1.engagement", "Column1.associations", "Column1.metadata"}),
#"Expanded Column1.engagement" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.engagement", {"id", "createdAt", "lastUpdated", "createdBy", "modifiedBy", "ownerId", "type", "timestamp"}, {"Column1.engagement.id", "Column1.engagement.createdAt", "Column1.engagement.lastUpdated", "Column1.engagement.createdBy", "Column1.engagement.modifiedBy", "Column1.engagement.ownerId", "Column1.engagement.type", "Column1.engagement.timestamp"}),
#"Expanded Column1.associations" = Table.ExpandRecordColumn(#"Expanded Column1.engagement", "Column1.associations", {"contactIds", "companyIds", "dealIds", "ownerIds"}, {"Column1.associations.contactIds", "Column1.associations.companyIds", "Column1.associations.dealIds", "Column1.associations.ownerIds"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1.associations", {"Column1.associations.contactIds", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column1.associations.companyIds", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Column1.associations.dealIds", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Extracted Values3" = Table.TransformColumns(#"Extracted Values2", {"Column1.associations.ownerIds", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Expanded Column1.metadata" = Table.ExpandRecordColumn(#"Extracted Values3", "Column1.metadata", {"disposition", "status", "subject", "taskType"}, {"Column1.metadata.disposition", "Column1.metadata.status", "Column1.metadata.subject", "Column1.metadata.taskType"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column1.metadata", "Engagement.Date", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Column1.engagement.timestamp]/1000)),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Engagement.Date", type date}})
in
#"Changed Type"

[johnelmer]

You will might consider the HubSpot Integration for Power BI, which is available in the HubSpot App Marketplace.  By using the connector, you will be able to create an automated data refresh schedule so that each time you open Power BI, the most recent HubSpot data is used to update your report.

HubSpot APIs are updated frequently, so using a managed third party app will eliminate the challenges of maintaining your own code.

[SprinkleData]

Hi @KevanGallagher ,

Why don't you try a platform which integartes data from Hubspot and uses Power BI for reporting purposes. Our company, Sprinkle Data has built a platform for this specific purpose. Furthermore, you can use our solution for reporting as well for without any extra cost.

Regards

Sam

[WendyGoh]

Hi @KevanGallagher,

While I'm not too sure why you're receiving duplicates results but just to be 100% sure, could you run the endpoint through a rest client such as Postman to see if you're getting duplicates as well? If you aren't receiving duplicates, it is likely the code logic that is causing the duplication. In this case, when looking at the for and while loop, it looks to be that it should work just fine.

To further debug this, could you do a console log of the results in the for loop and the while loop to see what are the results that is passing through and at which point it is causing the duplication? 

As for the error message, I believe it is because you set this line of code: 

#"Expanded Column1.metadata" = Table.ExpandRecordColumn(#"Extracted Values3", "Column1.metadata", {"disposition", "status", "subject", "taskType"}, {"Column1.metadata.disposition", "Column1.metadata.status", "Column1.metadata.subject", "Column1.metadata.taskType"}),

The reason is because, not every engagemnent type is a 'TASK'. There will be type such as 'NOTE' and 'CALL' and for these engagement types, under the metadata key json, there is no 'status'/'subject'/'taskType' key. There is only a 'body' key. 

Hence, if you'd like to pull the 'status', 'subject', 'taskType' key, you would have to use a if else statement to check if the type is == 'TASK'.