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

Hi Grant,

please check out this code:

 

let
Pagination = List.Skip(List.Generate( () => [Last_Key = 0, Counter=0], // Start Value
   		each  [Counter]<4, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/parties?page="&Text.From([Last_Key])&"",[Headers=[Authorization="Bearer WAWjGWU6Kbl4o9TeGMRw5i52+kvSiz9xfQe+vNTxlcjw61R7RZYa4HxvdT8TSlDG"]])), // retrieve results per call
     			Last_Key = [Last_Key]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = Table.FromRecords(WebCall[parties]) // steps of your further query
     			//Value = #"Converted to Table"{0}[Value] // last step of your further queries
                      ] 
   		,each [Table]
) ,1),
    Custom1 = Table.Combine(Pagination)
in
    Custom1

It returns results, but I couldn't spot any NextKey, so you might have to limit the number of pages manually.

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

Grant
Frequent Visitor

Hi Imke

 

Thank you for your rapid response. As you will probably gather from the questions to follow, I'm a Power Query/BI novice. the code you provided has pointed me in the right direction, and with some minor modifications, I get what I want - see code below.

let
Pagination = List.Skip(List.Generate( () => [Page = 1, Counter=0], // Start Value
   		each  [Counter]<50, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/parties?perPage=100&page="&Text.From([Page])&"",[Headers=[Authorization="Bearer WAWjGWU6Kbl4o9TeGMRw5i52+kvSiz9xfQe+vNTxlcjw61R7RZYa4HxvdT8TSlDG"]])), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = Table.FromRecords(WebCall[parties]) // steps of your further query
     			//Value = #"Converted to Table"{0}[Value] // last step of your further queries
                      ] 
   		,each [Table]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"id", "firstName", "lastName", "createdAt"}, {"id", "firstName", "lastName", "createdAt"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Column1", {"id"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [id] <> null)
in
    #"Filtered Rows"

What I'm actually trying to accomplish is to code so that I dont have to limit the number of pages manually. I want to stop the execution when the number of results returned are zero.

 

You will also note that I have had to modify the code remove duplicates and null values in order to arrive at the expected result i.e. 241 records.

 

Hope you can assist.

 

Kind Regards - Grant

Hi Grant,

please try this:

 

let
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Page = 1, Counter=0], // Start Value
   		each  Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/parties?perPage=100&page="&Text.From([Page])&"",[Headers=[Authorization="Bearer WAWjGWU6Kbl4o9TeGMRw5i52+kvSiz9xfQe+vNTxlcjw61R7RZYa4HxvdT8TSlDG"]])), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = Table.FromRecords(WebCall[parties]) // steps of your further query
                      ] 
   		,each [Table]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"id", "firstName", "lastName", "createdAt"}, {"id", "firstName", "lastName", "createdAt"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Column1", {"id"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [id] <> null)
in
    #"Filtered Rows"

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 am almost managing to implement the below query in my work. I have previously managed to use something similar by defining a set number of pages - however it requires a lot of maintenance as the number of total pages per query increases periodically. I have used your below query, however I am encountering two errors situationally:

 

1.  Some of the tables contain Records as separate columns, but when there is a row with a 'null'/blank instead of [Record] (i.e. there is no record for that field) the whole row turns into an error. After playing around a bit, I believe the problem is in how 'Pagination' creates the tables(as when I click on the step they already are an error), not sure how to fix it though? The final query I am using:

 

let
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Pages = 1, Counter=0], // Start Value
   		each  Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("link"&page=1", [Query=[page=Text.From([Pages])]])), // retrieve results per call
     			Pages = [Pages]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = Table.FromRecords(WebCall[customers]) // steps of your further query
                      ] 
   		,each [Table]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

2. I have an API which has an returns an 'Error 404 page not found' when it reaches a page that doesn't exist but rather than loading all the data so far, it just errors out. I previously had problems as well with my manual version of pagination and had to use two less pages than maximum (i.e. if the error was at page 157 I used 155 which also pulled page 156). Any thoughts on how to handle this?

 

Many thanks,

Daniel

Just as a quick update, I managed to Bypass #1 by not pulling the column creating an error from 

Table = Table.FromRecords(WebCall[customers],{"Col1","Col2",.."Colx"}) 

 However ideally I'd still like to use the records where they exist. Also if the error will pop up in another column I might not spot it. Ideally there would be some kind of error handling for null values?

 

Thanks,

Daniel

Hi Daniel,

although you described your problem very detailled, I still have the impression that I've missed some details. Please try the following alternative:

 

let
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Pages = 1, Counter=0], // Start Value
   		each  Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("link"&page=1", [Query=[page=Text.From([Pages])]])), // retrieve results per call
     			Pages = [Pages]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = WebCall[customers] // steps of your further query
                      ] 
   		,each [Table]
) ,1),
    #"Converted to Table" = List.Transform(Source, each Table.FromRecords(_)),
    Custom1 = Table.FromList(#"Converted to Table", Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Custom1

This shifts the merge of the records out of the List.Generate which might give a better basis for debugging.

 

re 1) Where are Records as colunmns? How does that look like - could you post a screenshot please?

re2) Is there by any chance a field in your record that indicates the next page (then one could stop once that goes blank)? I must admit that I'm having difficulties to understand what that actually means here: If the query (List.Generate) gets page after page - then how can the API take this away at the end?

 

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

Ahh, I wrote a long reply then accidentally did not click post 😞 

 

Re #1 please find below screen shot: 

 

Error.PNG

The interesting thing is that the column to the left also expands as a Record and then I can expand into multiple other columns - however I don't have the option to expand the "default_address" column at all. 

I tried the change you suggested, however it errors with 'We cannot convert a value of type List to type Table.' in the Pagination step. 

 

Re #2: I am using the exact same M code as previously posted, I am just referring a different page.  I believe it's something related to the API page being buggy, and instead of returning a blank page it returns somekind of error which trips the M code off?

 

Many Thanks 

You can solve the first problem with this technique: http://www.thebiccountant.com/2017/07/25/how-to-expand-a-column-that-cannot-be-expanded-in-power-bi-...

 

Any chance to send pics around the 2nd problem?

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! Still not sure how to bypass the 'Error' rows though before getting to expand. 

 

Please find #2 pics below:

Error nr2.PNG

 

Page 158 works fine, but when I try to load the whole query it gives the above error and doesn't load anything at all. It's only this specific page that errors out for some reason.

 

Thanks

1) You can either replace errors by a default value. That will keep the rows with errors.

 

image.png

The other option is to remove rows with errors:

image.png

 

Re your pic 2: Wouldn't removing rows with errors works as well here?

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

1) for all the errors, I actually need all the information from the other columns except the one that is error-ing out (which is actually blank/null). What I am not sure about is why the 'Error' is extending for the whole row, rather than being in only the cell/column with problems 

 

2) It was a good thought, however when it's a record as in the picture above it doesn't recognize it as an error row and 'Remove errors' or 'Replace errors' basically doesn't do anything. When I expand the column and use 'Remove errors' the query still doesn't load at all 😞 

 

Thanks

Yes, unfortunately there are 2 different types of errors and you seem to have got those who "infect" their neighborhood. Hard to come up with workarounds without actually getting my hands on it. My only suggestion is to reduce the transformations within List.Generate to the minimum (as suggested in one of my previous posts) and trying to circumnavigate the errors after that.

Does your WebCall actually return the data in the table-format that you've shown in your pic or is that the result of a further transformation-step already?

 

re 2) If you can transform the list to a table (like you've done) it should be possible to remove its last item instead. What does:

List.Range(Pagination, 0, List.Count(Pagination)-1)

return (instead of your current step "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

What does this return? (Removed any non-essential parts)

let
Pagination = List.Generate( () => [Table =  #table({}, {{}}) ,Pages = 1, Counter=0], // Start Value
   		each  Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("link"&page=1", [Query=[page=Text.From([Pages])]])), // retrieve results per call
     			Pages = [Pages]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = WebCall[customers] // steps of your further query
                      ] 
)
in     Pagination

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

In case someone else has similar problems: The solution for this case looked like so:

 

let
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Pages = 1, Counter=0], // Start Value
   		each  Table.RowCount([Table])>0 or [Counter]=0 , // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("link"],Query=[page=Text.From([Pages])]])),
// retrieve results per call
     			Pages = [Pages]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = try Table.FromRecords(WebCall[events]) otherwise Table.FromList({}) // steps of your further query
                      ]   ,
                each [Table] 		
), 1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

The Table expression contains an error-handler that returns an empty table in case of error. (

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

Would it be possible to create the same kind of error handling for the following code you helped me with a couple of years ago?

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://service-now.com/api/now/table/incident?sysparm_limit=500&sysparm_offset=1", 
                                    [Query=[sysparm_offset =Text.From([Page])]])), // retrieve results per call
     			Page = [Page]+500,
     			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"}, {"WebCall"}),
    #"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
    #"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result"),

in
    #"Expanded result"
Anonymous
Not applicable

Hi @ImkeF,

 

Hi All,

 

I am pulling data from REST API that is paginated with the below M Query,

 

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("http://py-soconnect.fusesport.com/api/members/for-season/250/?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"

However the last record is a ERROR record and the error message is as follows,

 

DataSource.Error: Web.Contents failed to get contents from 'http://py-soconnect.fusesport.com/api/members/for-season/250/?page=246' (404): Not Found
Details:
    DataSourceKind=Web
    DataSourcePath=http://py-soconnect.fusesport.com/api/members/for-season/250
    Url=http://py-soconnect.fusesport.com/api/members/for-season/250/?page=246

 I am unable to load the data into the model. Any help with this be highly appreciated.

 

Thanks in advance.

Hi @Anonymous,

there are different ways to handle errors in Power Query (see here for example https://blog.gbrueckl.at/2013/12/error-handling-in-power-query/) 

 

Skipping the last element of the list (that contains the error) might solve it already.

Or you can integrate a try..otherwise statement in your WebCall:

 

WebCall = try Json.Document(Web.Contents("http://py-soconnect.fusesport.com/api/members/for-season/250/?page="&Text.From([Page])&"")) otherwise #table({""}, {{""}}), // retrieve results per call 

there you might need to replace the empty table with a table that contains the column names of your results.

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 @ImkeF,

 

Many thanks for your solution. I tried the following code and able to pull 245 records but there are 246 records and the last record is being ignored.

 

let
Pagination = List.Skip(List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
   		each try Record.Field([WebCall],"next")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("http://py-soconnect.fusesport.com/api/members/for-season/250/?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"

 Could you please suggest a way around for this.

 

Thanks in advance.

I thought your problem was an error-message in the last item.

The code you're using skips the first element of the list. So this would return the full list:

 

let
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
   		each try Record.Field([WebCall],"next")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("http://py-soconnect.fusesport.com/api/members/for-season/250/?page="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
)),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

If that doesn't work and still the last element of your items is missing, you have to check the condition under which the next iteration happens: 

 

each try Record.Field([WebCall],"next")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
   		

That has to match your specific API 

 

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

xxyb720
Frequent Visitor

Hi ImkeF,

 

I have the same issue. I try to use the code I found on page 1 but it doesn't work for me since my source is JSON but the sample code in page 1 was XML. My source looks like this: https://shopname.myshopify.com/admin/api/2019-10/products.json?limit=250

Do you know what I should do to create a query that paginates?

 

 

let
    /*
      Purpose: Request all timesheet records from Dovico      
    */

    /*
      Generate a list of XML objects based on the Dovico's pagination of its return set
      The initial URL is:  https://api.dovico.com/TimeEntries/?version=5
      Pagination is controlled by return randomly-generated values in URI attributes PrevPageURI and NextPageURI
      The limit in each direction is denoted by the value "N/A"
      Date filtering can be introduced by using:  https://api.dovico.com/TimeEntries/?daterange=2016-04-01%202016-04-15&version=5
      See the Dovico API for more information:  http://apideveloper.dovico.com/Time+Entries
    */
    DataList = List.Generate(
                    ()=> [SourceURI="https://api.dovico.com/TimeEntries/?version=5",ImportedXML=""],
                    each Text.PositionOf([SourceURI],"N/A") = -1,
                    each [
        Source = Web.Contents([SourceURI],[Headers=[#"Authorization"="WRAP access_token=""client=CLIENT_TOKEN&user_token=USER_TOKEN"""]]),
        ImportedXML = Xml.Tables(Source,null,1252),
        ChangeType = Table.TransformColumnTypes(ImportedXML,{{"PrevPageURI", type text}, {"NextPageURI", type text}}),
        SourceURI = Record.Field(Table.First(ChangeType),"NextPageURI"),
        TimeEntries = ChangeType{0}[TimeEntries],
        TimeEntry = TimeEntries{0}[TimeEntry]
        ],
    each [[SourceURI],[ImportedXML]]
    ),

    /*
      Now expand and shape the list of XML objects into a single table of data
    */
    ConvertToTable = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"ImportedXML"}, {"ImportedXML"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([ImportedXML] <> "")),
    #"Expanded ImportedXML" = Table.ExpandTableColumn(#"Filtered Rows", "ImportedXML", {"TimeEntries"}, {"TimeEntries"}),
    #"Expanded TimeEntries" = Table.ExpandTableColumn(#"Expanded ImportedXML", "TimeEntries", {"TimeEntry"}, {"TimeEntry"}),
    #"Expanded TimeEntry" = Table.ExpandTableColumn(#"Expanded TimeEntries", "TimeEntry", {"Sheet", "Client", "Project", "Task", "Employee", "Date", "TotalHours", "Description"}, {"Sheet", "Client", "Project", "Task", "Employee", "Date", "TotalHours", "Description"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded TimeEntry",{{"Date", type date}, {"TotalHours", type number}, {"Description", type text}}),
    #"Expanded Project" = Table.ExpandTableColumn(#"Changed Type1", "Project", {"Name"}, {"Project.Name"}),
    #"Expanded Task" = Table.ExpandTableColumn(#"Expanded Project", "Task", {"Name"}, {"Task.Name"}),
    #"Expanded Employee" = Table.ExpandTableColumn(#"Expanded Task", "Employee", {"Name"}, {"Employee.Name"}),
    #"Expanded Client" = Table.ExpandTableColumn(#"Expanded Employee", "Client", {"Name"}, {"Client.Name"}),
    #"Expanded Sheet" = Table.ExpandTableColumn(#"Expanded Client", "Sheet", {"Status"}, {"Sheet.Status"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Sheet",{{"Client.Name", "Customer"}, {"Sheet.Status", "Approval status"}, {"Project.Name", "Project"}, {"Task.Name", "Task"}, {"Employee.Name", "Raw Name"}, {"TotalHours", "Effort (hrs)"}}),
    #"Added Week Ending" = Table.AddColumn(#"Renamed Columns", "Week ending", each Date.EndOfWeek([Date],Day.Saturday), type date),
    #"Added Name" = Table.AddColumn(#"Added Week Ending", "Name", each Text.Combine({List.Last(Text.Split([Raw Name],",")), List.First(Text.Split([Raw Name],","))}, " "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Name",{"Raw Name"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Name", Text.Trim}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Trimmed Text",{"Name", "Date", "Week ending", "Customer", "Project", "Task", "Effort (hrs)", "Description", "Approval status"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Description", "Approval status"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Date", Order.Descending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Customer", type text}, {"Project", type text}, {"Task", type text}})
in
    #"Changed Type"

  

Hi @xxyb720 

the technique of pagination is irrespective of the returned format. 
If you have problems with parsing out the needed values from your JSON, you would have to post sample data here and let me know which information you need from it to further pass into the pagination logic.

 

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

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.