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/
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/
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
- Anonymous5 years agoNot applicable
I am using the same pagination for my Dovico API, thanks to you for making it easy but when I publish and try to refresh, I get an error - "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
- Data source for Query1
Discover Data Sources"Please help if you faced a similar situation. Thanks
- ImkeF5 years agoCommunity Champion
Hi Anonymous ,
you probably need to use the relative path like described in here:
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/- Anonymous5 years agoNot applicable
ImkeF I try to cut the URL but I keep getting an error, please help.
= let
Source = Xml.Tables(Web.Contents("https://api.dovico.com/TimeEntries",
[RelativePath="/?daterange=2020-01-01%202025-12-31&version=5"],
[Headers=[#"Authorization"="WRAP access_token=""client="&DovicoAccessToken&"&user_token="&DovicoUserToken&""""]])),
Data0 = Source{0}[Data]
in
Source