how to create a query that paginates?

Hi Anonymous ,

This can be sorted by following the below steps. 

You'll require 4 add ons i.e., 1 parameter  and 3 functions. Replace the information suiting your organization URL.

1. Parameter: URL

e.g., https : // [Replace with your organization URL] / jira

1. Function 01:  FetchPage

let

FetchPage = (url as text, pageSize as number, skipRows as number) as table =>

let

//Here is where you run the code that will return a single page

contents = Web.Contents(URL&"/rest/api/2/search",[Query = [maxResults= Text.From(pageSize), startAt = Text.From(skipRows)]]),

json = Json.Document(contents),

Value = json[issues],

table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

table meta [skipRows = skipRows + pageSize, total = 500]

in

FetchPage

1. Function: FetchPages

let

FetchPages = (url as text, pageSize as number) =>

let

Source = GenerateByPage(

(previous) =>

let

skipRows = if previous = null then 0 else Value.Metadata(previous)[skipRows],

totalItems = if previous = null then 0 else Value.Metadata(previous)[total],

table = if previous = null or Table.RowCount(previous) = pageSize then

FetchPage(url, pageSize, skipRows)

else null

in table,

type table [Column1])

in

Source

in

FetchPages

1. Function: GeneratebyPage

(getNextPage as function, optional tableType as type) as table =>

let

listOfPages = List.Generate(

() => getNextPage(null),

(lastPage) => lastPage <> null,

(lastPage) => getNextPage(lastPage)

),

tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),

firstRow = tableOfPages{0}?,

keys = if tableType = null then Table.ColumnNames(firstRow[Column1])

else Record.FieldNames(Type.RecordFields(Type.TableRow(tableType))),

appliedType = if tableType = null then Value.Type(firstRow[Column1]) else tableType

in

if tableType = null and firstRow = null then

Table.FromRows({})

else

Value.ReplaceType(Table.ExpandTableColumn(tableOfPages, "Column1", keys), appliedType)

Once the above is created, the next step will be is to use the function and parameters to retrieve the content. you can start with the below and expand the fields of choice to create your report.

Main query:

let

Source = FetchPages("", 500),

#"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"key", "fields"}, {"key", "fields"}),

in

#"Expanded Column1"

Hope this helps you to build the report. Please note this query is only the search information and if you'd need change log information you can modify that in the 2nd function listed above.

Good luck.

Cheers,

Anand