Forum Discussion

JordanPearson's avatar
JordanPearson
Helper II
4 years ago
Solved

Pull data from Hubspot into PBI via API

Hi all,    We have an existing project to improve our reporting capabilities around the data we hold in Hubspot (CRM platform).    The biggest problem we face right now is that we don't have a co...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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
    Source

    Notice: #"ApiKey" part can be changed, you can check the API document definition first.

    Regards,

    Xiaoxin Sheng

Cayshin's avatar
Cayshin
Frequent Visitor
1 year ago

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().