Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello there
I've a Dovico web API that I need to access to pull down all records. For clarity, I'm calling the Time Entries function.
The API lists request restritcions as:
...limited to 5 calls per second and 1000 results returned per call. This means 5000 records per second are returned for GET calls...When the page results are returned they will include the Previous Page URI and Next Page URI. If the Next Page URI returns the value of “N/A” then that’s the last page
When I use the following Web.Contents call (tokens obscured for security) I get a neat result set of 1,000 rows:
= Web.Contents("https://api.dovico.com/TimeEntries/?version=5",[Headers=[#"Authorization"="WRAP access_token=""client=XXX&user_token=YYY"""]])
In other words I'm sending only one request. If I run = Xml.Tables(<source above>,null,1252) I can clearly see both PrevPageURI and a NextPageURI data.
I've spent a huge amount of time googling my heart out, and trying all sorts of things, but thus far I'm a numpty.
Has anyone advice for me on how I can exploit the NextPageURI returned by the query to then repeat the query until I hit N/A?
I appreciate your support!
Solved! Go to Solution.
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...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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?
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.
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...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Just to clarify, does something like this support being "published" up to powerbi.com to run as a service vs desktop mode?
I saw another post that broke out into many functions and someone said "you can't upload functions" (truncated).
TIA
To my knowedge if it works in desktop with privacy settings on, it should also work in the service.
There are issues in the service with HTML-string and this article has good tips for it: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Ok, So I went and setup a Get JSON data source and it works in Power BI desktop but when I publish it I see;
Dataset - had error. Unable to refresh the model because it references an unsupported data source.
Then I found the help page about data refresh here https://docs.microsoft.com/en-us/power-bi/refresh-data#what-can-be-refreshed and it says:
Note
If you are using the Web.Page function, you do need a gateway if you have republished the dataset or your report after November 18th, 2016.
So apparently this doesn't work, I need a gateway. I'm trying to publish this for other users outside my org and I can't ask them to install a gateway.
It didn't occur to me that this was what you were asking for, as this is a general requisite for web queries and not related to the pagination issue of this thread.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Good point, I was hijacking! I'm not good at forums..
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
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.
Discover Data Sources"Please help if you faced a similar situation. Thanks
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
After I made it work, somehow I still see the error but now asking for web credentials? I have an only access token for Dovico.
@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
Hi @Anonymous ,
the Web.Contents function takes max. 2 arguments. So you must combine everything after the URL into one record:
= 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.