I am doing Dovico API integration with Power BI, I get the data that I need and successfully publish but I am unable to refresh or set up the gateway as there is 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"Power Query am using:
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"
