Forum Discussion
how to create a query that paginates?
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"
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-bi/
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"
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=t95jkkcqimdgkkchkjsnwh&method=get")),
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?
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.
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
