Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the results.
For instance, if you send:
https://api.hubapi.com/deals/v1/deal/all?hapikey=demo
at the very end of the query, you see the following JSON:
"hasMore":false "offset":27939158
so, if hasMore is true, the NEXT query should look like this:
https://api.hubapi.com/deals/v1/deal/all?hapikey=demo&offset=27939158
and then, we would want to repeat the process until hasMore comes back with false.
i'm completely new to power bi, so would love to know how to handle this type of query process.
in another language, this would just be do { } while (hasMore == false);
or something like that...
@noorul unfortunately not, i'm not really a developer and I couldn't quite figure it out and ran out of time to look at it.
I'm still very much interested in a solution though as at the moment I have to produce a report daily which i'd rather not have to 😄
I have a function that is queried in a table query.
This is the function:
let AccountData = (Timestamp as text, UnitId as number) =>
let
Source = Json.Document(Web.Contents("https://qa.api.remoni.com/v1/Data?orderbydesc=Timestamp&Timestamp=le(3000-01-01)&UnitId=eq(1)&AggregateType=eq(Hour)&top=10000",
[Query=[orderbydesc="Timestamp",Timestamp="le("&Text.From(Timestamp)&")",UnitId="eq("&Text.From(UnitId)&")",AggregateType="eq(Hour)",top="10000"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"AccountId", "UnitId", "SensorId", "DataType", "AggregateType", "Timestamp", "Value", "Base"}, {"AccountId", "UnitId", "SensorId", "DataType", "AggregateType", "Timestamp", "Value", "Base"})
in
#"Expanded Column1"
in AccountData
It is being queried for Timestamp and UnitId in another query.
My problem is that we have much more than 10.000 rows which is the limit in an API call.
To get around it the API offers a Top function: Returns the maximum number of entities.
and a Skip-function: Skips this number of entities
How can I do pagination in this scenario? It seems simple but after a few days I haven't resolved it...
The table-query (it is the query that is loaded to the model) looks like this and the function is called here:
#"Added Custom1" = Table.AddColumn(#"Added Custom", "UnitData", each AccountData([Today], [UnitId])),
The total Table-Query:
let
Source = Json.Document(Web.Contents("https://qa.api.xxxxx.com/v1/Units?orderby=UnitId&Account.AccountId=eq("&AccountId&")&top=10000")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"UnitId", "Name", "Account", "UnitType", "Tags"}, {"UnitId", "Name", "Account", "UnitType", "Tags"}),
#"Expanded Account" = Table.ExpandRecordColumn(#"Expanded Column", "Account", {"AccountId"}, {"AccountId"}),
#"Expanded UnitType" = Table.ExpandRecordColumn(#"Expanded Account", "UnitType", {"Name"}, {"Name.1"}),
#"Expanded Tags" = Table.ExpandListColumn(#"Expanded UnitType", "Tags"),
#"Expanded Tags1" = Table.ExpandRecordColumn(#"Expanded Tags", "Tags", {"Text"}, {"Text"}),
#"Grouped Rows" = Table.Group(#"Expanded Tags1", {"UnitId"}, {{"Tag", each _, type table}}),
#"Added Custom4" = Table.AddColumn(#"Grouped Rows", "Tags", each Table.Column([Tag], "Text")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom4", {"Tags", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Expanded Tag" = Table.ExpandTableColumn(#"Extracted Values", "Tag", {"Name", "AccountId", "Name.1"}, {"Name", "AccountId", "Name.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Tag",{{"Name.1", "SensorType"}}),
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"UnitId"}),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Today", each Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "UnitData", each AccountData([Today], [UnitId])),
#"Removed Errors1" = Table.RemoveRowsWithErrors(#"Added Custom1", {"UnitData"}),
#"Expanded UnitData" = Table.ExpandTableColumn(#"Removed Errors1", "UnitData", {"DataType", "AggregateType", "Timestamp", "Value", "Base"}, {"DataType", "AggregateType", "Timestamp", "Value", "Base"}),
#"Filtered Rows1 - Keep external-temperature-1" = Table.SelectRows(#"Expanded UnitData", each Text.Contains([DataType], "external-temperature-1")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1 - Keep external-temperature-1",{{"Timestamp", type datetime}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[DataType]), "DataType", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each Text.EndsWith([Base], "C")),
#"Removed Duplicates1" = Table.Distinct(#"Filtered Rows", {"UnitId", "Timestamp"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Duplicates1",{{"external-temperature-1", "Value"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Value", type number}, {"AccountId", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Name", "Name - Copy"),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Duplicated Column", "Text Before Delimiter", each Text.BeforeDelimiter([#"Name - Copy"], "_"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Name - Copy"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Text Before Delimiter", "Anlæg"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns2", "Value rounded3", each Number.Round ([Value],3, RoundingMode.AwayFromZero))
in
#"Added Custom2"
Hi @Larstc75 ,
this depends on the specific API. You should try to create URL with these parameters (in a hardcoded fashion) and try them out with Postman for example.
I could then help you to replace the hardcoded parameters with the correct M-code.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi!
Total coding newbie here trying to get more than 1000 entries from XML Tables in Azure Table Storage using Web API and a Shared Access Key.
What I've learned so far:
What I guess I need:
(Then of course I have like 30 transformations to get the data into the shape I need it to present it to the users...)
Many thanks for any help!
Quick follow-up:
Got some of the headers through:
let Källa = Value.Metadata(Web.Contents("https://[account].table.core.windows.net/[tablename]?st=2018-11-08T12%3A41%3A00Z&se=2018-12-10T12%3A41%3A00Z&sp=r&sv=2017-04-17&tn=history0debug&sig=[Shared Access Key]")) as record, #"Konverterad till tabell" = Record.ToTable(Källa) in Källa
Which gives me:
Navigating to the "Headers" record only gives me "application/atom+xml;type=feed;charset=utf-8" but not the continuation tokens needed.
Have no experience with that API, so no help on this one unfortunately.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
No worries.
I got access to a secondary key and could access the Table Storage through the "normal" API instead, and, from what I can tell, now I get all the entities I query for, without pagination.
Cheers!
Okay, so I'm the OP on this and unfortunately I lost track of this post for a couple YEARS... whoops.
But now I got back to it and in looking through all the responses, it seems to be a very hot topic.
My original question was about HubSpot CRM specifically and pagination in general.
I have finally found a solution, which I will post here for everyone else's benefit.
But first, a couple notes:
At any rate, with all the above caveats, here is the query which works for me and includes all the expanded tables/fields to get to the individual values.
None of the other solutions worked for me, but I used several of them to get to this point. Thanks to everyone for your help.
If I am able to speed this up using the items in #10 above, I will post that solution as well.
let // Start Values Pagination = List.Skip(List.Generate( () => [hsOffset = 0, Counter = 0, isMore = true], // Condition under which the next execution will happen each [isMore] <> false, // retrieve results per call each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/paged?hapikey=[ENTER-YOUR-API-KEY-HERE]&limit=250&offset=" & Text.From(hsOffset))), hsOffset = try [WebCall][offset] otherwise 0, isMore = if [Counter] <1 then null else [WebCall][#"hasMore"], // internal counter Counter = [Counter] + 1 // ,Table = Table.FromRecords(WebCall[deals]) ] // ,each [Table] ),1), #"Converted to Table1" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"WebCall", "hsOffset", "isMore", "Counter"}, {"Column1.WebCall", "Column1.hsOffset", "Column1.isMore", "Column1.Counter"}), #"Expanded Column1.WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.WebCall", {"deals", "hasMore", "offset"}, {"Column1.WebCall.deals", "Column1.WebCall.hasMore", "Column1.WebCall.offset"}), #"Expanded Column1.WebCall.deals" = Table.ExpandListColumn(#"Expanded Column1.WebCall", "Column1.WebCall.deals"), #"Expanded Column1.WebCall.deals1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals", "Column1.WebCall.deals", {"dealId", "isDeleted"}, {"Column1.WebCall.deals.dealId", "Column1.WebCall.deals.isDeleted"}), // THIS IS THE SECOND API CALL FOR DEAL DETAILS #"Added Custom" = Table.AddColumn(#"Expanded Column1.WebCall.deals1", "DealInfo", each Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/" & Text.From([Column1.WebCall.deals.dealId]) & "?hapikey=[ENTER-YOUR-API-KEY-HERE]"))), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Column1.WebCall.offset", Int64.Type}, {"Column1.WebCall.hasMore", type logical}}), #"Expanded DealInfo" = Table.ExpandRecordColumn(#"Changed Type", "DealInfo", {"properties"}, {"DealInfo.properties"}), #"Expanded DealInfo.properties" = Table.ExpandRecordColumn(#"Expanded DealInfo", "DealInfo.properties", {"dealname", "createdate", "hubspot_owner_id", "hs_analytics_source", "deal_temperature", "hs_createdate", "dealtype", "closed_lost_reason", "closedate", "pipeline", "notes_last_contacted", "dealstage", "hs_analytics_source_data_2", "hs_analytics_source_data_1", "amount"}, {"DealInfo.properties.dealname", "DealInfo.properties.createdate", "DealInfo.properties.hubspot_owner_id", "DealInfo.properties.hs_analytics_source", "DealInfo.properties.deal_temperature", "DealInfo.properties.hs_createdate", "DealInfo.properties.dealtype", "DealInfo.properties.closed_lost_reason", "DealInfo.properties.closedate", "DealInfo.properties.pipeline", "DealInfo.properties.notes_last_contacted", "DealInfo.properties.dealstage", "DealInfo.properties.hs_analytics_source_data_2", "DealInfo.properties.hs_analytics_source_data_1", "DealInfo.properties.amount"}), #"Expanded DealInfo.properties.dealstage" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties", "DealInfo.properties.dealstage", {"value"}, {"DealInfo.properties.dealstage.value"}), #"Expanded DealInfo.properties.hs_analytics_source_data_2" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.dealstage", "DealInfo.properties.hs_analytics_source_data_2", {"value"}, {"DealInfo.properties.hs_analytics_source_data_2.value"}), #"Expanded DealInfo.properties.hs_analytics_source_data_1" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source_data_2", "DealInfo.properties.hs_analytics_source_data_1", {"value"}, {"DealInfo.properties.hs_analytics_source_data_1.value"}), #"Expanded DealInfo.properties.amount" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source_data_1", "DealInfo.properties.amount", {"value"}, {"DealInfo.properties.amount.value"}), #"Expanded DealInfo.properties.notes_last_contacted" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.amount", "DealInfo.properties.notes_last_contacted", {"value"}, {"DealInfo.properties.notes_last_contacted.value"}), #"Expanded DealInfo.properties.pipeline" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.notes_last_contacted", "DealInfo.properties.pipeline", {"value"}, {"DealInfo.properties.pipeline.value"}), #"Expanded DealInfo.properties.closedate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.pipeline", "DealInfo.properties.closedate", {"value"}, {"DealInfo.properties.closedate.value"}), #"Expanded DealInfo.properties.closed_lost_reason" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.closedate", "DealInfo.properties.closed_lost_reason", {"value"}, {"DealInfo.properties.closed_lost_reason.value"}), #"Expanded DealInfo.properties.dealtype" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.closed_lost_reason", "DealInfo.properties.dealtype", {"value"}, {"DealInfo.properties.dealtype.value"}), #"Expanded DealInfo.properties.hs_createdate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.dealtype", "DealInfo.properties.hs_createdate", {"value"}, {"DealInfo.properties.hs_createdate.value"}), #"Expanded DealInfo.properties.deal_temperature" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_createdate", "DealInfo.properties.deal_temperature", {"value"}, {"DealInfo.properties.deal_temperature.value"}), #"Expanded DealInfo.properties.hs_analytics_source" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.deal_temperature", "DealInfo.properties.hs_analytics_source", {"value"}, {"DealInfo.properties.hs_analytics_source.value"}), #"Expanded DealInfo.properties.hubspot_owner_id" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source", "DealInfo.properties.hubspot_owner_id", {"value"}, {"DealInfo.properties.hubspot_owner_id.value"}), #"Expanded DealInfo.properties.createdate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hubspot_owner_id", "DealInfo.properties.createdate", {"value"}, {"DealInfo.properties.createdate.value"}), #"Expanded DealInfo.properties.dealname" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.createdate", "DealInfo.properties.dealname", {"value"}, {"DealInfo.properties.dealname.value"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded DealInfo.properties.dealname",{{"DealInfo.properties.createdate.value", Int64.Type}}), #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"DealInfo.properties.closedate.value", Int64.Type}, {"DealInfo.properties.notes_last_contacted.value", Int64.Type}, {"DealInfo.properties.hs_createdate.value", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Create.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, -4, 0, [DealInfo.properties.createdate.value]/1000)), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Create.DateTime", type datetime}}) in #"Changed Type3"
Actually, I was able to clean this up and use a single API call to get all the needed properties.
The code below makes one single API call that executes quickly and returns most if not all of the standard HubSpot properties.
It also converts all the Unix Epoch timestamps to regular DateTime values.
And, it assigns the correct data type to each column, so it will sort/filter and play nice in reports.
I think this is ultimately what I was after.
I hope this helps someone else looking to use Power BI for HubSpot and/or anyone running into this pagination issue.
let // ENTER API KEY HERE apiKey = "[API-KEY-GOES-HERE]", // GO THROUGH PAGINATION Pagination = List.Skip(List.Generate( () => [hsOffset = 0, Counter = 0, isMore = true], // Condition under which the next execution will happen each [isMore] <> false, // retrieve results per call each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/paged" & "?hapikey=" & myApiKey & "&limit=250" & "&properties=notes_last_updated" & "&properties=dealname" & "&properties=amount" & "&properties=closedate" & "&properties=num_associated_contacts" & "&properties=createdate" & "&properties=pipeline" & "&properties=hubspot_owner_id" & "&properties=num_contacted_notes" & "&properties=hs_lastmodifieddate" & "&properties=hs_analytics_source" & "&properties=notes_last_contacted" & "&properties=hubspot_owner_assigneddate" & "&properties=deal_temperature" & "&properties=dealstage" & "&properties=hs_createdate" & "&properties=hs_object_id" & "&properties=hs_analytics_source_data_2" & "&properties=hs_analytics_source_data_1" & "&properties=num_notes" & "&properties=dealtype" & "&offset=" & Text.From(hsOffset))), hsOffset = try [WebCall][offset] otherwise 0, isMore = if [Counter] <1 then null else [WebCall][#"hasMore"], // internal counter Counter = [Counter] + 1
] ),1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall"}, {"Column1.WebCall"}), #"Expanded Column1.WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.WebCall", {"deals"}, {"Column1.WebCall.deals"}), #"Expanded Column1.WebCall.deals" = Table.ExpandListColumn(#"Expanded Column1.WebCall", "Column1.WebCall.deals"), #"Expanded Column1.WebCall.deals1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals", "Column1.WebCall.deals", {"dealId", "isDeleted", "properties"}, {"Column1.WebCall.deals.dealId", "Column1.WebCall.deals.isDeleted", "Column1.WebCall.deals.properties"}), #"Expanded Column1.WebCall.deals.properties" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals1", "Column1.WebCall.deals.properties", {"notes_last_updated", "dealname", "closedate", "num_associated_contacts", "createdate", "pipeline", "hubspot_owner_id", "num_contacted_notes", "hs_lastmodifieddate", "hs_analytics_source", "notes_last_contacted", "hubspot_owner_assigneddate", "deal_temperature", "dealstage", "hs_createdate", "hs_object_id", "hs_analytics_source_data_2", "hs_analytics_source_data_1", "num_notes", "dealtype", "amount"}, {"notes_last_updated", "dealname", "closedate", "num_associated_contacts", "createdate", "pipeline", "hubspot_owner_id", "num_contacted_notes", "hs_lastmodifieddate", "hs_analytics_source", "notes_last_contacted", "hubspot_owner_assigneddate", "deal_temperature", "dealstage", "hs_createdate", "hs_object_id", "hs_analytics_source_data_2", "hs_analytics_source_data_1", "num_notes", "dealtype", "amount"}), #"Expanded notes_last_updated1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals.properties", "notes_last_updated", {"value"}, {"notes_last_updated.value"}), #"Expanded dealname" = Table.ExpandRecordColumn(#"Expanded notes_last_updated1", "dealname", {"value"}, {"dealname.value"}), #"Expanded closedate" = Table.ExpandRecordColumn(#"Expanded dealname", "closedate", {"value"}, {"closedate.value"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded closedate",{{"closedate.value", Int64.Type}, {"notes_last_updated.value", Int64.Type}}), #"Expanded num_associated_contacts" = Table.ExpandRecordColumn(#"Changed Type", "num_associated_contacts", {"value"}, {"num_associated_contacts.value"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded num_associated_contacts",{{"num_associated_contacts.value", Int64.Type}}), #"Expanded createdate" = Table.ExpandRecordColumn(#"Changed Type1", "createdate", {"value"}, {"createdate.value"}), #"Expanded pipeline" = Table.ExpandRecordColumn(#"Expanded createdate", "pipeline", {"value"}, {"pipeline.value"}), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded pipeline",{{"createdate.value", Int64.Type}}), #"Expanded hubspot_owner_id" = Table.ExpandRecordColumn(#"Changed Type2", "hubspot_owner_id", {"value"}, {"hubspot_owner_id.value"}), #"Changed Type3" = Table.TransformColumnTypes(#"Expanded hubspot_owner_id",{{"hubspot_owner_id.value", Int64.Type}}), #"Expanded num_contacted_notes" = Table.ExpandRecordColumn(#"Changed Type3", "num_contacted_notes", {"value"}, {"num_contacted_notes.value"}), #"Changed Type4" = Table.TransformColumnTypes(#"Expanded num_contacted_notes",{{"num_contacted_notes.value", Int64.Type}}), #"Expanded hs_lastmodifieddate" = Table.ExpandRecordColumn(#"Changed Type4", "hs_lastmodifieddate", {"value"}, {"hs_lastmodifieddate.value"}), #"Changed Type5" = Table.TransformColumnTypes(#"Expanded hs_lastmodifieddate",{{"hs_lastmodifieddate.value", Int64.Type}}), #"Expanded hs_analytics_source" = Table.ExpandRecordColumn(#"Changed Type5", "hs_analytics_source", {"value"}, {"hs_analytics_source.value"}), #"Expanded notes_last_contacted" = Table.ExpandRecordColumn(#"Expanded hs_analytics_source", "notes_last_contacted", {"value"}, {"notes_last_contacted.value"}), #"Changed Type6" = Table.TransformColumnTypes(#"Expanded notes_last_contacted",{{"notes_last_contacted.value", Int64.Type}}), #"Expanded hubspot_owner_assigneddate" = Table.ExpandRecordColumn(#"Changed Type6", "hubspot_owner_assigneddate", {"value"}, {"hubspot_owner_assigneddate.value"}), #"Changed Type7" = Table.TransformColumnTypes(#"Expanded hubspot_owner_assigneddate",{{"hubspot_owner_assigneddate.value", Int64.Type}}), #"Expanded deal_temperature" = Table.ExpandRecordColumn(#"Changed Type7", "deal_temperature", {"value"}, {"deal_temperature.value"}), #"Expanded dealstage" = Table.ExpandRecordColumn(#"Expanded deal_temperature", "dealstage", {"value"}, {"dealstage.value"}), #"Expanded hs_createdate" = Table.ExpandRecordColumn(#"Expanded dealstage", "hs_createdate", {"value"}, {"hs_createdate.value"}), #"Changed Type8" = Table.TransformColumnTypes(#"Expanded hs_createdate",{{"hs_createdate.value", Int64.Type}}), #"Expanded hs_object_id" = Table.ExpandRecordColumn(#"Changed Type8", "hs_object_id", {"value"}, {"hs_object_id.value"}), #"Changed Type9" = Table.TransformColumnTypes(#"Expanded hs_object_id",{{"hs_object_id.value", Int64.Type}}), #"Expanded hs_analytics_source_data_2" = Table.ExpandRecordColumn(#"Changed Type9", "hs_analytics_source_data_2", {"value"}, {"hs_analytics_source_data_2.value"}), #"Expanded hs_analytics_source_data_1" = Table.ExpandRecordColumn(#"Expanded hs_analytics_source_data_2", "hs_analytics_source_data_1", {"value"}, {"hs_analytics_source_data_1.value"}), #"Expanded num_notes" = Table.ExpandRecordColumn(#"Expanded hs_analytics_source_data_1", "num_notes", {"value"}, {"num_notes.value"}), #"Changed Type10" = Table.TransformColumnTypes(#"Expanded num_notes",{{"num_notes.value", Int64.Type}}), #"Expanded dealtype" = Table.ExpandRecordColumn(#"Changed Type10", "dealtype", {"value"}, {"dealtype.value"}), #"Expanded amount" = Table.ExpandRecordColumn(#"Expanded dealtype", "amount", {"value"}, {"amount.value"}), #"Changed Type11" = Table.TransformColumnTypes(#"Expanded amount",{{"amount.value", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type11", "CloseDate.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [closedate.value]/1000)), #"Changed Type12" = Table.TransformColumnTypes(#"Added Custom",{{"CloseDate.DateTime", type datetime}}), #"Added Custom1" = Table.AddColumn(#"Changed Type12", "CreateDate.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [createdate.value]/1000)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "LastModified.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [hs_lastmodifieddate.value]/1000)), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "LastContacted.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [notes_last_contacted.value]/1000)), #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom3", {{"LastContacted.DateTime", null}}), #"Added Custom4" = Table.AddColumn(#"Replaced Errors", "OwnerAssigned.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [hubspot_owner_assigneddate.value]/1000)), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "HsCreateDate.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [hs_createdate.value]/1000)), #"Changed Type13" = Table.TransformColumnTypes(#"Added Custom5",{{"CreateDate.DateTime", type datetime}, {"LastModified.DateTime", type datetime}, {"LastContacted.DateTime", type datetime}, {"OwnerAssigned.DateTime", type datetime}, {"HsCreateDate.DateTime", type datetime}}) in #"Changed Type13"
I am looking for a pagination solution too, and I am almost there but not quite....!
here is my code (and it feels very close):
let baseURL = "https://api.vworkapp.com/v4/jobs.xml", apiKey = "?api_key=xxxxxxxx", params = "&start_at=2018-10-15&end_at=2018-10-16", perPage = "&per_page=200", queryURL = baseURL & apiKey & params & perPage, PreFetchData = Xml.Tables(Web.Contents(queryURL)), PageCount = Number.FromText(PreFetchData{0}[#"Attribute:total_pages"]), GetPageData = (Index) => let XMLData = Xml.Tables(Web.Contents(queryURL & "&page=" & Index)) in XMLData, Data = List.Generate ( () => [i=1], each [i] < PageCount, each [ i=[i]+1, ResList = GetPageData( Number.ToText([i]) ) ], each [ResList] ) in Data
What this gets me is:
The Table records are spot on, I can expand those and they are exactly what I want, but there is an error in the first list item and I can't figure out why:
I can't seem to get past this error, can anyone put me on the right track to solving why i=1 would be a problem? The api accepts page=1 so I don't see what would be wrong....
EDIT: I have skipped over that row and everything else after that works as expected, so if I solve why i=1 is an issue then I am done!
@Anonymous looks to me that you are making it too complex...i think , in your case, u can paginate simply by calling a function. See this page
http://sqlcodespace.blogspot.com/2017/09/power-bipower-query-api-response.html
Thank you Reddy. That was the most elegant solution I found in my use case and worked like a charm.
I spent a while on this recently and finally cracked it. Here's some code that will retrieve all companies from Hubspot - some entries may be duplicated (I never figured that out) but a simple next query step of 'remove duplicates' does the trick. It can be used as the basis for all Hubspot API calls and probably similar pagination APIs:
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/companies/v2/companies/paged?hapikey=" & #"Hubspot API Key" & "&properties=name&properties=website&limit=250&offset=" & Text.From([Last_Key]) & "")), // retrieve results per call Last_Key = try [WebCall][offset] otherwise 0, IsMore = if [Counter] < 1 then null else [WebCall][#"has-more"], Counter = [Counter]+1, Table = Table.FromRecords(WebCall[companies]) ] ,each [Table] // selector ) ,1) // in // Pagination , Custom1 = Table.Combine(Pagination) in Custom1
Thanks for sharing @shad0wca7 ! I don't suppose you've updated this with the changes to HubSpot APIs? I'm using a PAT and I'm struggling to figure out hot to get it to work (as opposed to the APIKEY
Thank you @shad0wca7 This worked perfectly. All I had to do was adjust for the contacts API and modify [offset] to [#"vid-offset"], and the Table to be contacts instead of companies. I really appreciate it.
You're welcome! I spent a fair bit of time trying to get this to work reliably and it would be a shame not to share it!
@gotmike or @ImkeF I am using the HubSpot Contacts API to get all contacts from the CRM. I am running into the same issue with paging through the contact list. I have been reading through the comments and trying different approaches, but unfortunately without any luck. The parameter for &vidOffset can be used in the url to page through contacts, but needs to be sent in the second call and is based on the the vid-offset value in the first call. Any help on this would be greatly appreciated.
I'm too busy currently to look deeper into this, but please check this link: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks @ImkeF. I reviewed the link you posted and tried that code as part of my testing, but unfortunatly it doesn't work for my use case. I don't get a number of pages back in the response, instead I get a vid-offset number and a has-more value of true if there are additional contacts to page through. The vid-offset number that I get back on the first call is the max vid (hubspot contact id) in the list. This number needs to then be passed into the parameter &vidOffset in the url to get additional contacts. Each time the vid-offset will come back with another number until the has-more value no longer equals true. I will keep at it and see if I can figure it out. Thanks again.
Hello, I need help to load in Power BI more than 100 records from Airtable.
I do not understand this programming language, so even though I have reviewed all the examples I do not understand what I should do.
Here is my code, I did it using the power BI interface to import data and convert into a table.
I need help!!! Thank you
letOrigen = Json.Document(Web.Contents("https://api.airtable.com/v0/appWNGNQnupCwVItO/Proyectos?api_key=key")),#"Converted to Table1" = Record.ToTable(Origen),#"Removed Bottom Rows" = Table.RemoveLastN(#"Converted to Table1",1),#"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each [Value] <> "itrn4qcW1C96uVpbe/reczS133aB2AtJA3t"),#"Expanded Value" = Table.ExpandListColumn(#"Filtered Rows", "Value"),#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id", "fields", "createdTime"}, {"Value.id", "Value.fields", "Value.createdTime"}),#"Se expandió Value.fields" = Table.ExpandRecordColumn(#"Expanded Value1", "Value.fields", {"Id Jira", "Nombre", "Estado", "Horas Op", "Pasos a produccion", "HH est", "Fechas", "Resp", "Tipo plan", "Compañia", "Resolutor", "Tipo Proy", "Responsable", "Programacion des", "Trimestre", "Trabajo semanal", "Facturacion", "ExcluirPBI"}, {"Value.fields.Id Jira", "Value.fields.Nombre", "Value.fields.Estado", "Value.fields.Horas Op", "Value.fields.Pasos a produccion", "Value.fields.HH est", "Value.fields.Fechas", "Value.fields.Resp", "Value.fields.Tipo plan", "Value.fields.Compañia", "Value.fields.Resolutor", "Value.fields.Tipo Proy", "Value.fields.Responsable", "Value.fields.Programacion des", "Value.fields.Trimestre", "Value.fields.Trabajo semanal", "Value.fields.Facturacion", "Value.fields.ExcluirPBI"}),#"Reordered Columns" = Table.ReorderColumns(#"Se expandió Value.fields",{"Value.id", "Name", "Value.fields.Id Jira", "Value.fields.Nombre", "Value.fields.Estado", "Value.fields.Horas Op", "Value.fields.Pasos a produccion", "Value.fields.HH est", "Value.fields.Fechas", "Value.fields.Resp", "Value.fields.Tipo plan", "Value.fields.Compañia", "Value.fields.Resolutor", "Value.fields.Tipo Proy", "Value.fields.Responsable", "Value.fields.Programacion des", "Value.fields.Trimestre", "Value.fields.Trabajo semanal", "Value.createdTime"}),Personalizado1 = #"Reordered Columns"inPersonalizado1
This video might be a good start to get and understanding of what's required here: https://www.youtube.com/watch?v=vhr4w5G8bRA
You will have to transform your query into a function - this video might also help: https://www.youtube.com/watch?v=GgwXt4LVmsU)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries