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
