Pagination using Power BI


#1

Hi Team,

I'm currently using this query in MS Power Query, you can copy and paste, I've substituted the 'demo' key for mine.

let

     Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                                             WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.hubapi.com/contacts/v1/lists/all/contacts/all?hapikey=demo&count=250"))  else Json.Document(Web.Contents("https://api.hubapi.comts/all/contacts/all?hapikey=demo&count=250&vidOffset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
 ),1),
    Pagination1 = Pagination{0},
    Value = Pagination1[Value],
    contacts = Value[contacts]
in
    contacts

I can not make it paginate. One issue I'm having is if I declare [offset] to [vid-offset] I can an invalid identifier error. I believe this is the source of my problem but can't figure out a workaround. Would appreciate the help,

thanks

link to documentation API Doc for Contacts


#3

Hi @Brodie, happy to help. While I'm not familiar with MS Power Query / how it functions, I'll try to help in any way I can. If you're trying to access the vid-offset value, have you tried using ["vid-offset"] with quotes? I recently built a tool in NodeJS to paginate through contacts in a HubSpot Account to push the values from a a specific property into an array and count them. I had the same issue of accessing the has-more and vid-offset parameters, but not sure if this is exactly what you're seeing. Here's my repo: https://github.com/cbarley10/hubspot-pagination

MS Power Query seems like it runs code and puts it into a spreadsheet, but I could be vastly oversimplifying it's capabilities. If that's the case, I saw a pretty interesting article on Medium that does something similar if you want to try testing this out: https://medium.com/@alexisbedoret/create-a-hubspot-custom-dashboard-with-google-spreadsheet-and-data-studio-27f9c08ade8d


#4

Hi Connor,

Thanks for the assist.

The following 2 codes changes in the 4th row
[vid-offset] and ["vid-offset] both result in the same error

Invalid identifier - it doesn't like the quotes nor the -


#5

Hi @Brodie,

Here is what I use and it works in Power BI.

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/contacts/v1/lists/all/contacts/all?" & "hapikey=******" & "&property=firstname" & "&property=lastname" & "&property=createdate" & "&property=email" & "&propertyMode=value_and_history" & "&count=100" & "&vidOffset="&Text.From([Last_Key])&"", [Headers=[ContentType="application/json", Authorization="Bearer Token"]])),// retrieve results per call
Last_Key = try [WebCall][#"vid-offset"] otherwise 0,
IsMore = if [Counter] < 1 then null else [WebCall][#"has-more"],
Counter = [Counter]+1,
Table = Table.FromRecords(WebCall[contacts])
]
,each [Table] // selector
) ,1)

// in
// Pagination
,
Custom1 = Table.Combine(Pagination),

One thing to note, is the max contacts you can get with each call is 100. I see you have your &count set to 250. Not sure if that is an issue or not.


#6

Thanks so much for the reply @snamuth! This is awesome. Hopefully this solves your issue here, @Brodie


#7

Thanks very much for this, this definitely solved the pagination issue.

thanks again, and I hope this helps others #powerbi #dax #hubsput #powerquery

fyi, I removed the [Headers...token] worked without it.


#8

Thanks @snamuth this is really useful. It also works for the companies API. Just a quick note that if you want this to work in the PowerBI service you will need to use the relative path feature of Web.Contents as below

each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com",
[
RelativePath="companies/v2/companies/paged?hapikey=*****&limit=250&properties=name&properties=city&offset=" & Text.From([Last_Key])
]
)),

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])
]

One thing I have noticed is that this seems to return duplicate companies (each company appears twice). Also when debugging this query PowerBI is calling the API twice each loop (ie. each offset is called twice). Do you have any idea why this is?