Hubspot API to PowerBi for call logs

[KevanGallagher]

Hi all, 

I have an issue that I haven't been able to solve for a while now. Thanks to the help of this community I have been able to get all our call engagements into PowerBi so I can create a custom dashboard. Now when I pull all the data in I'm getting duplicates for every value and I can't seem to figure out why. Does anyone have an idea? 

here's my code:

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=XXXXX&count=100&since=1209... 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"

(Sorry I am aware this might be a little messy but don't know how to post it differently for now )

[SprinkleData]

Hi @KevanGallagher 

You can use Sprinkle Data for customised report generation. Sprinkle helps you integrate data from multiple sources, automate the data pipelines and build customised reports. Furthermore, you can use Sprinkle for report visualization as well. In short, you can eliminate, Power BI or any other reporting tool and use a single platform for end to end data management.

Regards

Sam

[WendyGoh]

Hi @KevanGallagher,

Looks like this is similar to the issue posted here: https://community.hubspot.com/t5/APIs-Integrations/HubSpot-engagements-in-Power-Bi-Dashboard/m-p/302... and I have responded in that thread.