Using Windsor.ai to get Hubspot Email Opens (events) into Power Bi

[user_k]

I use Windsor.ai successfully to get Email Campaign stats like open rate / click into Power Bi. So, no issues connecting Hubspot + Windsor.ai + Power Bi.

My issue is when trying to get specific email events through Windsor (such as opens by specific email address). I cannot figure out how to connect to Email IDs to campaign/marketing email IDs or name. So I have no idea which email the Open is referring to. Every other ID column I try to add just creates a new blank row.

I feel like I'm missing an obvious column which links Email IDs to Campaign Email IDs. Even after reviewing windsor.ai's field documentation.

I've also tried to directly connect to Hubspot via API in Power Bi. Using the instructions from "Cayshin" in this question. However, even when I reduce the limit I always get stuck with the error "Expression.Error: Evaluation resulted in a stack overflow and cannot continue." 

[v-sathmakuri]

Hi @user_k ,

We are following up once again regarding your query. Could you please confirm whether the issue has been resolved through your support ticket?

If so, we would appreciate it if you could share the resolution or any key insights here to benefit others in the community. If we don’t receive a response, we will proceed with closing this thread.

If you need further assistance in the future, feel free to start a new thread in the Microsoft Fabric Community Forum. We will be happy to support you there.

Thank you!!

[v-priyankata · Accepted solution]

Hi @user_k 
please raise a support ticker with Windsor.ai Support. Windsor.ai Help Desk

Thanks.

[v-priyankata]

Hi @user_k,

We are grateful for your participation in the Microsoft Fabric Community forum.

For an issue with Windsor.ai please raise a support ticker with Windsor.ai Support. Windsor.ai Help Desk

For the error Expression.Error: Evaluation resulted in a stack overflow and cannot continue , this might be because of Too Much Data from the source. Please try modifying the limits to get the less data.

if you want  any help in filtering the data using the API calls(URL's)  please reach out to hubspot support desk

If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[user_k]

Thank you @v-priyankata  - 

This is the API call I have added to Power Bi, I keep getting the Expression.Error even after reducing the limit from 1000 to 10. Do you have any advice how to limit this?

let
GetPages = (queryParams) =>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "email/public/v1/events", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer API KEY HERE"]]
)
),
Events = try Source[events] otherwise {},
NextOffset = try Source[#"offset"] otherwise null,
Next = if NextOffset <> null then [limit="10", eventType = "CLICK", offset = Text.From(NextOffset)] else null,
result = if Next <> null then Events & @GetPages(Next) else Events
in
result,

// Call GetPages function to fetch all pages
Fullset = GetPages([limit="10", eventType = "CLICK"]),

// Convert the result into a table
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"