Forum Discussion
Pull data from Hubspot into PBI via API
HI JordanPearson,
According to the error message, it seems like you directly input API key value into the data connector, right? AFAIK, this connector will require you to define what type of API key that sends to the API service. (normally they will be defined as 'key name' = 'key value' to use in the connector)
Here is the sample query:
let Source = OData.Feed( "<API URL>", null, [Headers = [ #"ApiKey" = "<YOUR API KEY>" ]] ) in SourceNotice: #"ApiKey" part can be changed, you can check the API document definition first.
Regards,
Xiaoxin Sheng
Here's how I'm getting the data into a query that is compatible with Power BI Online's scheduled refresh (i.e. it doesn't compain about "dynamic datasources"). Code is based on this other solution.
Here are some examples below.
Get all Marketing Emails
let
GetPages = (queryParams)=>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "marketing-emails/v1/emails", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[objects],
Next = [limit="1000", offset = Source[#"offset"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,
Fullset = GetPages([limit="1000"]),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Get all CLICK events
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 INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[events],
Next = [limit="1000", eventType = "CLICK", offset = Source[#"offset"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,
Fullset = GetPages([limit="1000", eventType = "CLICK"]),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Get all Contacts
let
GetPages = (queryParams)=>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "crm/v3/objects/contacts", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[results],
Next = [limit="100", properties = {"hubspot_owner_id", "firstname" , "lastname", "company" , "email"}, after = Source[#"paging"][#"next"][#"after"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,
Fullset = GetPages([limit="100", properties = {"hubspot_owner_id", "firstname" , "lastname", "company" , "email"}]),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
#"Converted to Table"
I'm not hitting the API rate limit, so I don't need to worry about it, but if you are then refer to Ray_Brosius's answer above on using Function.InvokeAfter().
Hello Cayshin - does the above strategy still work for you?
I'm trying to use the "Get all CLICK events" code in my Power bi report (via a new blank query, and adding the code + my Hubspot access token to the advanced query editor), but each time I try I get stuck on the "Edit Credentials" piece. I try using the Web Api credential option by adding my access key again, but get this error:
Do you have any advice?
Yes, it still works.
If you get the "Edit Credentials" prompt, you'll want to select the Anonymous option.