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.
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...
@ImkeF - I have been following your guidance throughout this thread, your help has been really insightful (and a huge benefit to the Power BI community). However, I am still struggling with how to isolate the value that gives me the next page for the API call, here is the response from the API:
{
"metadata": {
"filtering": {},
"paging": {
"per_page": 100,
"current_page": 2,
"next_page": 3, <-- Need this value
"prev_page": 1,
"total_pages": 81,
"total_count": 8001
},
"sorting": {
"sort_by": "updated_at",
"sort_direction": "DESC NULLS LAST"
}
},
"data": [
{...}]
In your code from the prior entries, you suggested doing this:
let
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
each try Record.Field([WebCall],"???next_page???")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://api.sample.com/v2/activities/actions.json?per_page=100&include_paging_counts=true&page="&Text.From([Page])&"",[Headers=[Authorization="<APIKEY>"]])), // retrieve results per call
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
)),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"Column1.WebCall", "Column1.Page", "Column1.Counter"})
in
#"Expanded Column1"
My question is the "try Record.Field([WebCall]."next_page") step, how do I grab the value under metadata/paging/next_page in the json? My Power M game is not that strong.
Hi @Anonymous
you can navigate JSON records fields with square brackets like so:
#"Parsed JSON"[metadata][paging][next_page]
please see attached file
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
@ImkeF - I spoke too soon. I am having trouble with the following line from the code you gave in previous posts:
let
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
each try Record.Field([WebCall],)<>null otherwise false or [Counter]=0, //how do I reference the [metadata][paging][next_page] value in this expression?
each [ WebCall = Json.Document(Web.Contents("https://api.sample.com/v2/activities/stuff.json?per_page=100&include_paging_counts=true&page="&Text.From([Page])&"",[Headers=[Authorization=""]])), // retrieve results per call
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
))
in
Pagination
Currently, when I run this it fails to return even the first page of data. Sorry, I had to remove the API key and real URL, but the structure is similar to the post above.
Hi @Anonymous
please try:
... Record.Field( [WebCall] [metadata][paging], "next_page" ) ....
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, @ImkeF I have gone through the entire post and successfully able to paginate the report. But I am facing this strange issue where my query keeps on running even if it hits the last records.
Below is my query what am I doing wrong here?
let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0], // Start Value
each List.Count([WebCall][result])>0 or [Counter]=0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task?sysparm_limit=100&sysparm_offset=1",
[Query=[sysparm_offset =Text.From([Page])]])),
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
) ,1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"}, {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"})
in
#"Expanded result1"
Hi @Anonymous
this could be due to the result list not being actually empty. Could it be that you need to navigate further down to it?
Otherwise please paste screenshot of the empty list in the allegedly empty items. (add "or [Counter] < YourNumber to stop the execution at a feasable number)
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 @ImkeF Thanks for your reply
My query keeps on running even if I change the counter parameter. There are close to 6000 records in the table but query runs beyond these.
let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0], // Start Value
each List.Count([WebCall][result])>0 or [Counter]<3, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task?sysparm_limit=100&sysparm_offset=1",
[Query=[sysparm_offset =Text.From([Page])]])),
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
) ,1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded WebCall", {"result", each Text.Combine(List.Transform(_, Text.From)), type text}),
result = #"Extracted Values"{0}[result]
in
result
Also not sure how to add images here
Hi @Anonymous
you have to adjust the condition like so for debugging purposes:
each List.Count([WebCall][result])>0 and [Counter]<3, // Condition under which the next execution will happen
click the camera-icon to add pictures:
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 @ImkeF ,
I was able to run the query successfully suing the condition each List.Count([WebCall][result])>0 and [Counter]<3.
Now my question is how I make sure that it runs till the last record in the table. Here are the images
Hi @Anonymous
Question is how this looks like for the first item that shall cause the iteration to stop:
Will we see a 0 in Page for example? Or a null?
Then you'd adjust your condition like so:
each [WebCall][result] {0}[Page]>0 and [Counter]<3
This retrieves the first element from the list (a record) and gets the figure in field "Page".
For some reason, your API doesn't return an empty list, you have to find out what else there is to build the condition on.
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 and for others, this might help. I made some changes to the query and now it is running as expected to return the total number of records from the table.
Below is my query
let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0],
each List.Count([WebCall][result])>0 or [Counter]=0,
each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task?sysparm_limit=100&sysparm_offset=1",
[Query=[sysparm_offset =Text.From([Counter])]])), Page = [Page]+1, Counter = [Counter]+100]
) ,1
),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"}, {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"})
in
#"Expanded result1"
Hi @Anonymous ,
thanks and happy to help. But the JSON you've posted isn't valid.
Please paste valid JSON and I will follow this up.
Thanks.
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
@ImkeF - Sorry about that, forgot the closing brackets.
{
"metadata": {
"filtering": {},
"paging": {
"per_page": 100,
"current_page": 2,
"next_page": 3,
"prev_page": 1,
"total_pages": 81,
"total_count": 8034
},
"sorting": {
"sort_by": "updated_at",
"sort_direction": "DESC NULLS LAST"
}
},
"data": [
{
"id": 4926173,
"to": "XXXXXXXXXXX",
"duration": 106,
"sentiment": "5-Referred to Other Contact",
"disposition": "1-Talked to Contact",
"created_at": "2020-02-26T16:44:37.875353-05:00",
"updated_at": "2020-02-26T16:44:38.109183-05:00",
"recordings": [
{
"url": "https://sample.com",
"status": "completed",
"recording_status": "completed"
}
],
"user": {
"_href": "https://sample.com",
"id": 1555
},
"action": null,
"called_person": {
"_href": "https://sample.com",
"id": 699379
},
"crm_activity": {
"_href": "https://sample.com",
"id": 23514150
},
"note": {
"_href": "https://sample.com",
"id": 1784343
},
"cadence": null,
"step": null
}
]
}
Thanks!
Thanks Imke
Additionally, in the case where some rows were error-ing out, taking out
each [Table]
Allows the step by step expansion of the columns, which no longer contains error Rows.
I suspect the issue is Table.FromRecords in the below code:
Table.FromRecords(WebCall[events])
When there are record/columns within the API that are blank, Table.FromRecords doesn't know how to treat them and hence creates a whole row of errors.
Thanks for all the help!
1) I think I mentioned it but it was lost in the long comment - the change you previously suggested gives a ''We cannot convert a value of type List to type Table.' error. I've been trying the same idea but I am not sure what else to cut out (also because I'm only at 75% understanding on how the whole query works - I don't understand some of the little details what they do)
2)errors out altogether - I've just noticed now but even though 'converted to table' works, when i click on the 'Pagination' step, the name is 'Error':
would it work to add an error check statement in this part of the query :
each Table.RowCount([Table])>0 or [Counter]=0 + or [Table] is error? I already tried to remove last row from Table = Table.FromRecords(WebCall[events]) but it doesn't do anything at all
Thanks for the effort!
Hi Imke
Absolutely brilliant. That is exactly what I needed. Thank you for your assistance and rapid turnaround.
Kind Regards - Grant
Hi,
I've been trying to solve my query using this thread, but not really sure how to go about it.
Basically, the database I'm connected to cuts off values at 500 per query, so im doing 0-500, then 500-1000, then 1000-1500 and so on.
Here is the code (with changed url/api key for security reasons)
let
Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start=0&limit=1499&api_token=xxxxxx")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "owner_id", "name", "open_deals_count", "re "Column1.cc_email"})
in
#"Expanded Column1"
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
As you see, in the data above, I try to query the data from start=0 to limit=1499, but i still only get values 0 to 500. I have about 1400 values in the actual data.
Any help would be much appreciated.
If anyone can let me know, it would be amazing, thanks a lot!
Hi @cartman21,
if you know beforehand that you need to split up your calls into 3 chunks and don't have to retrieve values from the current call to make the next one, you can use a much simpler logic than the List.Generate-versions in this thread.
You start with creating a table that holds all the different URL-parameters that you then pass on to a function. Expand that resulting table & you're done:
let
// table with your query intervalls
Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}}),
// Call Function
CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish])))
// Function
Function = (Start as text, Finish as text) =>
let
Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Limit&"&api_token=xxxxxx")),data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "owner_id", "name", "open_deals_count", "re "Column1.cc_email"})
in
#"Expanded Column1"
in
CallFunction
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
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |