how to create a query that paginates?

[gotmike]

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

[Anonymous]

@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 😄

[Larstc75]

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"

[ImkeF]

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.

[PerK]

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:

• I can use the continuation tokens in HTTP response headers in the URL/URI to get to the next set of 1000. From what I can tell (in my limited abilities)
• PBI doesn't show me the headers anywhere in the GUI (?)

What I guess I need:

• A way to get my query to read the HTTP response headers
• Parse out the "NextPartitionKey" and "NextRowKey"
• Insert the keys in the next request
• Understand how to loop the above and implement in a way that hopefully works in PBI Service...

(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!

[PerK]

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.

[ImkeF]

Have no experience with that API, so no help on this one unfortunately.

[PerK]

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!

[gotmike]

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:

1. My goal here was to get DEAL info from HubSpot CRM. Presumably, the same process would work for any other info from HubSpot like CONTACTS or COMPANIES. Also would probably work for many others who have a similar use case.
2. Like many APIs out there, they PAGINATE the results, by default they will give you 100 per API call, but you can request up to 250 per call.
3. Like many APIs that paginate, they also give you an OFFSET, so you provide that offset on subsequent calls to get additional pages.
4. Like many APIs that paginate, they will give you a boolean indicator if there are more or not.
5. Like many APIs out there, the HubSpot API breaks data up into separate API calls, so the first one gets you a list of IDs, which you then must use to make a subsequent call for each individual item.
6. Like many APIs out there, the times sent are in unix timestamps. My query converts these to regular time, albeit hard-coded to Eastern Daylight Time.
7. I believe all the fields I'm using in this are standard HubSpot fields, but there is a possibility there are some custom ones. If so, then you may get an error and have to remove those.
8. I use two HubSpot API endpoints here, the first one that gives me a list of ALL DEALS with their dealId is https://api.hubapi.com/deals/v1/deal/paged. The second one that gives me individual deal properties is https://api.hubapi.com/deals/v1/deal/[dealId].
9. Be aware that depending on your number of DEALS, this can take a really long time to execute. It is definitely not "efficient" and is not optimized in any way. I have over 4,000 deals and it takes maybe a half-hour or so.
10. Also, be aware that HubSpot has a daily limit on API calls of about 40,000. So running this query with 4,000 deals will eat up a lot of your API calls.
11. I believe I can solve #7 and #8 above by using some combination of two things to optimize this query:
    1. Specifying specific PROPERTIES in the initial API call may prevent me from having to make a separate call for each individual deal, if I know the exact properties I want
    2. Instead of using the above API endpoint for ALL DEALS, I can use the one for RECENTLY CREATED DEALS and specify a start date. This endpoint is https://api.hubapi.com/deals/v1/recent/created.

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"

[gotmike]

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"

[Anonymous]

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]

@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

[PBI_KLo]

Thank you Reddy. That was the most elegant solution I found in my use case and worked like a charm.

[shad0wca7]

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

[JackSelman]

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

[snamuth]

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.

[shad0wca7]

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!

[snamuth]

@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. 

[ImkeF]

Hey @Aracelli and @snamuth,

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/ 

[snamuth]

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. 

[Aracelli]

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

let

    Origen = 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"

in

    Personalizado1

[ImkeF]

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)