Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gotmike
Frequent Visitor

how to create a query that paginates?

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

214 REPLIES 214

So yes, here we see the problem: You were expecting the WebCall to return a record with a field "requests" in it, but here it returns a list instead. Therefore your original code: Table = Table.FromRecords(WebCall[requests]) couldn't work.

 

Now expand the column "WebCall" to see what is actually returned instead.

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

That is amazing ImkeF. Now I am able to see the columns and values as expected. And the code seems to work, as I can get more than 100 rows.

 

But I need to know the amount of counts to get all the rows. It would be so smart if that was not nessecary, because the data source will change often.

Try this:

 

let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
   		each List.Count([WebCall])>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

 

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

This is amazing ImkeF and is working perfectly:-) Thank you so much.

 

I have another system I need to do the same with, but the last "automatic" code does not work.

 

It works perfectly fine with the code where I must know the amount of rows:

let
Pagination = List.Skip(List.Generate( () => [Page = 1, Counter=0], // Start Value
   		each  [Counter]<30, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+10,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

But not with the code where it automatically stops when there is no more rows:

let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
   		each List.Count([WebCall])>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_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

I get the following error:

2017-11-22_09-24-29.png

Pleased to hear 🙂

 

In your new case, the WebCall doesn't return its results in the format of a list, but in a record instead. So we can modify the looping-conditions that it counts the number of fields from the last record: If there are any, then continue else stop like this:

 

 

let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>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_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

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

That sounds very good, but I still recieve almost identical error with the new code:

2017-11-22_10-15-23.png

That's probably because the starting value has to be adjusted to record-format as well 🙂

 

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>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_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

 

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

You are the very best ImkeF. It works perfectly. Thank you so much for helping me out. It means the world

Oh one thing is unfortunately wrong:-( It keeps loading rows, eventhough it has reached the end.

What does that mean exactly? That the query never stops?

 

For debugging could you please do the following?:

 

1) Evaluate the number of exections needed and adjust the value in the looping condition accordingly:

 

each List.Count(Record.FieldNames([WebCall]))>0 or [Counter]<=YourNumber+1

 

2) Send a screenshot of the last result so that we can see what kind of record is returned. If it's not an empty record, but a record with fields but emty values, we have to find a different logic for the stop-condition in the loop-statement.

 

 

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

 

Sorry for the late reply.

 

I chose another to table to fetch data from that has 67 rows and adjusted the code accordingly:

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 0, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>0 or [Counter]<=68, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/u_subcategory?sysparm_limit=1&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			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")
in
    #"Expanded result"

 

Here is a screenshot of the result that shows that the code keeps running eventhough I udjusted the [Counter]:

 

2017-11-24_09-30-12.png

 

And a screenshot where I have expanded the result:

2017-11-24_09-34-20.png

So how about this then?:

 

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 0, Counter=0], // Start Value
   		each [WebCall]<>null or [Counter]<=68, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/u_subcategory?sysparm_limit=1&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			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")
in
    #"Expanded result"

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

 

It is still the same issue. Keeps loading rows with "NULL" values after the last row with data.

Oh my, this is a bit tedius without the actual data... 😉

How about this?:

 

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 0, Counter=0], // Start Value
   		each [WebCall][result]<>null or [Counter]<=68, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/u_subcategory?sysparm_limit=1&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			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")
in
    #"Expanded result"

 

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

I'm posting the code that we ended up here, because it includes the syntax that will also work in PBI service. This might be useful for other readers as well. The previous syntax in this thread will probably only work in Desktop. Please check this blogpost to find out why: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

 

 

 

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/incident?sysparm_limit=1&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")
in
    #"Expanded result"

 

 

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

Anonymous
Not applicable

hi Imke,

I am working on similar req like the ones in this post. i am new to coding. I need to pull data from web api which has limit of 2000 rows. but I need help to pull all the records.

this is what the query looks like

 

let
Source = Json.Document(Web.Contents("https://infotech.attask-ondemand.com/attask/api/v9.0/user/search?fields=*&apiKey=t95jkkcqimdgkkchkjs...")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"latestUpdateNoteID", "layoutTemplateID", "licenseType", "locale", "logTimeInDays", "loginCount", "managerID", "mobilePhoneNumber", "myInfo", "passwordDate", "persona", "phoneExtension", "phoneNumber", "Column1.hasDocuments", "Column1.hasNotes", "Column1.hasProofLicense", "Column1.hasReservedTimes", "Column1.homeGroupID", "Column1.homeTeamID", "Column1.isActive"})
in
#"Expanded Column1"

In general you do this by dynamically creating the URLs with page parameters who will then be called.

Therefor you need a URL-structure where you can integrate the row numbers to pull. 

Is this returned in the first result by any chance?

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

Anonymous
Not applicable

yes, with above url gives 100 rows by default.It is  a workfront application url and I need to pull all rows from all pages.

So you are saying, that url needs to have some page information like parameters? i only have that url right now. Can you suggest what the next steps be?

You have to look it up in the API-documentation. Try a search for "pagination" there.

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

I created a query with pagination to retrieve issues from GitHub, following Mark Tiedemann's helpful post here. I thought I'd share for anyone else who may be needing to report on GitHub issues.

 

Here's my code:

 

let
GitHubAPI = "https://api.github.com/search/issues?",
Repository = "q=repo:OwnerName/RepositoryName+",
SearchCriteria = "type:issue+state:open&",
BaseUrl = GitHubAPI & Repository & SearchCriteria,

EntitiesPerPage = 100,

GetJson = (Url) =>
let RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,

GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url),
Count = Json[#"total_count"]
in Count,

GetPage = (Index) =>
let PerPage = "per_page=" & Text.From(EntitiesPerPage),
Page = "page=" & Text.From(Index + 1),
Url = BaseUrl & PerPage & "&" & Page,
Json = GetJson(Url),
Value = Json[#"items"]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount -1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in Table

 

Grant
Frequent Visitor

Hi

 

I've been watching this thread, in particular the conversation between Imke & kroll, in the hope that I may get the help I need. It seems that solution provided by Imke to kroll might do the trick for me, I want to return all records. I've attempted to reverse engineer the code but it returns an error. I've provided the code with the credentials to a test data source in the hope that someone might be able to assist.

 

let
Pagination = List.Skip(List.Generate( () => [Last_Key = 1, Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/parties?page='"&[Last_Key]&"'",[Headers=[Authorization="Bearer WAWjGWU6Kbl4o9TeGMRw5i52+kvSiz9xfQe+vNTxlcjw61R7RZYa4HxvdT8TSlDG"]])), // retrieve results per call
     			Last_Key = if [Counter]<=1 then 1 else WebCall[lastKey] ,// determine the LastKey for the next execution
     			Counter = [Counter]+1,// internal counter
     			#"Converted to Table" = Record.ToTable(WebCall), // steps of your further query
     			Value = #"Converted to Table"{1}[Value] // last step of your further queries
                      ], 
   		each [Value]),1),
    Pagination1 = Pagination{0}
in
    Pagination1

 

Kind Regards - Grant

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.