Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I want to use this Hubspot API to get data into the Power BI:
https://legacydocs.hubspot.com/docs/methods/contacts/get_contacts
I am using following M Query to do that:
let
APIURL = "https://api.hubapi.com",
MaxRecords = 100,
Total =
let
Source = Json.Document(Web.Contents( APIURL & "/contacts/v1/contacts/statistics?hapikey=my-api-key")),
Total = Source[contacts]
in
Total,
OP=
let
data =
List.Generate(
() => [ a = 0, c = 0 ],
each [a] <= Total,
each [
a = [a] + MaxRecords,
c = [a]
]
),
op = Table.FromRecords(data)[a]
in
op,
vidOffset=0,
Loop = List.Transform( OP, each
let
source = Json.Document(Web.Contents(APIURL,
[
RelativePath = "/contacts/v1/lists/all/contacts/all?hapikey=my-api-key" & "&vidOffset=" & Number.ToText(vidOffset) & "&count=" & Number.ToText(MaxRecords) & "&loop=" & Number.ToText(_)
]
))
in
source
),
#"Converted to Table" = Table.FromList(Loop, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"contacts", "has-more", "vid-offset"}, {"contacts", "has-more", "vid-offset"}),
#"Expanded contacts" = Table.ExpandListColumn(#"Expanded Column1", "contacts"),
#"Expanded contacts1" = Table.ExpandRecordColumn(#"Expanded contacts", "contacts", {"vid", "canonical-vid", "properties", "identity-profiles"}, {"vid", "canonical-vid", "properties", "identity-profiles"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Expanded contacts1", "properties", {"firstname", "company", "lastname"}, {"firstname", "company", "lastname"}),
#"Expanded firstname" = Table.ExpandRecordColumn(#"Expanded properties", "firstname", {"value"}, {"value"}),
#"Expanded company" = Table.ExpandRecordColumn(#"Expanded firstname", "company", {"value"}, {"value.1"}),
#"Expanded lastname" = Table.ExpandRecordColumn(#"Expanded company", "lastname", {"value"}, {"value.2"})
in
#"Expanded lastname"
I have 37,500+ records in Hubspot Contacts.
This code is fetching first 100 records again and again. We are getting these two fields in each API call:
"vid-offset" is some random number and not giving proper pagination.
The problem is with the next API calls I made, where we need to make next calls ony if "has-more" is true and we need to pass "vid-offset" value which we received in previous call as API parameter "vidOffset".
Can anyone help me with editing my M query please?
For ref: I got this connector which is working perfectly but I do not want to use connector and gateway setup.
https://github.com/ranmax123/powerbi-custom-connectors/tree/master/HubSpot
Thanks in advance.
Solved! Go to Solution.
Got the solution. The code to get all the deals is follow:
let
apiUrl = "https://api.hubapi.com",
dealsProperties = "hs_forecast_amount,hs_manual_forecast_category,hs_forecast_probability,amount,amount_in_home_currency,closedate,createdate,dealname,dealstage,dealtype,pipeline,hubspot_owner_id,num_notes,num_contacted_notes,closed_lost_reason,closed_won_reason",
propertiesQString = "&properties=" & Text.Replace(dealsProperties, ",", "&properties="),
suffixUrl = "/deals/v1/deal/paged?hapikey=my-api-key-here&limit=250&includeAssociations=true" & propertiesQString & "&offset=",
Source =
let
jobsJsonPaginated = List.Generate( () =>
[pageResult = null, nextOffset = 0, counter = 1],
each [counter] <= 1 or [nextOffset] <> 0,
each [pageResult = try
let
response = Json.Document(Web.Contents(apiUrl, [RelativePath = suffixUrl & Text.From([nextOffset])]))
in
response
otherwise null,
nextOffset = try pageResult[offset] otherwise 0,
counter = [counter] + 1],
each [pageResult]),
jobsJsonPaginated2 = List.Skip(jobsJsonPaginated, 1)
in
jobsJsonPaginated2,
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Got the solution. The code to get all the deals is follow:
let
apiUrl = "https://api.hubapi.com",
dealsProperties = "hs_forecast_amount,hs_manual_forecast_category,hs_forecast_probability,amount,amount_in_home_currency,closedate,createdate,dealname,dealstage,dealtype,pipeline,hubspot_owner_id,num_notes,num_contacted_notes,closed_lost_reason,closed_won_reason",
propertiesQString = "&properties=" & Text.Replace(dealsProperties, ",", "&properties="),
suffixUrl = "/deals/v1/deal/paged?hapikey=my-api-key-here&limit=250&includeAssociations=true" & propertiesQString & "&offset=",
Source =
let
jobsJsonPaginated = List.Generate( () =>
[pageResult = null, nextOffset = 0, counter = 1],
each [counter] <= 1 or [nextOffset] <> 0,
each [pageResult = try
let
response = Json.Document(Web.Contents(apiUrl, [RelativePath = suffixUrl & Text.From([nextOffset])]))
in
response
otherwise null,
nextOffset = try pageResult[offset] otherwise 0,
counter = [counter] + 1],
each [pageResult]),
jobsJsonPaginated2 = List.Skip(jobsJsonPaginated, 1)
in
jobsJsonPaginated2,
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Works great for deals. Have a contacts / companies version?
Looks like your API has an offset parameter. Please see this video for a different/simpler way to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You can also take a look at the following document about the help functions about processing pagination:
Helper functions for M extensions for Power Query connectors | Microsoft Docs
Best Regards
Paul
Thanks a lot for reply @V-pazhen-msft ,
This looks like connectors, but I am looking for M query so I can get the data directly into power bi rather then using the gateway.
Also I have almost written following code which gives me the page by page data but problem is only that once all the pages are fetched it agains starting the fetching, so it seems never ending loop.
The API documentation is here: https://legacydocs.hubspot.com/docs/methods/deals/get-all-deals
let
APIURL = "https://api.hubapi.com/deals/v1/deal/paged?&limit=100&hapikey=my-api-key&offset=",
Pagination = List.Skip(List.Generate( () => [Last_Key = "0", Counter=0],
each [Last_Key] <> null,
each [Last_Key = try if [Counter]=0 then "" else [Source][Value][offset] otherwise null,
//each [Last_Key = try if Number.ToText([Source][Value][offset])="0" then null else [Source][Value][offset] otherwise null,
Source = try if [Counter]=0 then
Json.Document(Web.Contents(APIURL & Last_Key))
else
Json.Document(Web.Contents(APIURL & Number.ToText(Last_Key))),
Counter = [Counter]+1
],
each [Source]
),1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"HasError", "Value"}, {"Column1.HasError", "Column1.Value"}),
#"Expanded Column1.Value" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Value", {"deals", "hasMore", "offset"}, {"deals", "hasMore", "offset"})
in
#"Expanded Column1.Value"
The output it gives is as follow:
Required rows are only first 12 but it goes on and on repeating same 12 rows.
If you can please review and suggest to end the loop. Meanwhile I will go thru the link you sent.
Thanks a log.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |