Forum Discussion
how to create a query that paginates?
- 10 years ago
With List.Generate you should be able to "harvest" the list of URL's - you can even try to "harvest" the content at the same time. (But watch out: There's an error in the documentation: Result of last example should be {2, 4, 6, 8})
See Chris' article on how List.Generate works in general: http://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/
tempranello - What does the next page URL look like. Ideally, could you post the next page URL and then the next page URL after that?
- tempranello10 years agoAdvocate I
Sorry, I should have included that from the beginning.
I've generated these by running the initial query with the Web.Contents url as:
https://api.dovico.com/TimeEntries/?next=&version=5
...and then manually copying and pasting the next values and re-running the query.
https://api.dovico.com/TimeEntries/?next=M9286&version=5
...and again with the next value
https://api.dovico.com/TimeEntries/?next=M10703&version=5
...and again with the next value
https://api.dovico.com/TimeEntries/?next=M10801&version=5
They appear to be randomly generated and returned in each result set.
- ImkeF10 years agoCommunity Champion
With List.Generate you should be able to "harvest" the list of URL's - you can even try to "harvest" the content at the same time. (But watch out: There's an error in the documentation: Result of last example should be {2, 4, 6, 8})
See Chris' article on how List.Generate works in general: http://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/
- tempranello10 years agoAdvocate I
After replying to the wrong thread (here), I'll close this thread out. With ImkeF's support, I have produced a query that paginates through a dynamic volume of URLs and in doing so generates a list of XML objects that I can later expand into a single table. To help anyone else who bumps into a similar challenge in the future, here's what I did:
let /* 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" For testing, 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 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"}), /* Now shape the data, specifically for my use */ #"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}}), #"Added Team" = Table.AddColumn(#"Trimmed Text", "Team", each fnLookupTeam([Name],[Date],"Team",#"Staff movements lookup"), type text), #"Reordered Columns" = Table.ReorderColumns(#"Added Team",{"Name", "Team", "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"The expanding and shaping (including a few custom functions) are relatively specific to my use, but the code in general may be helpful to someone else.
Cheers