Forum Discussion
how to create a query that paginates?
Helper IThanks 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
Helper IJust 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
- ImkeF
Community Champion
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
Custom1This 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?
- DBa
Ahh, I wrote a long reply then accidentally did not click post :(
Re #1 please find below screen shot:
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
- ImkeF
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-and-power-query-in-excel/
Any chance to send pics around the 2nd problem?
