Hello,
- I need to keep the data for the past 24 hours that I refresh in power BI desctop (thats how I model my report and dataset)
- I have premium and pro account
- I have set RangeStart and RangeEnd parameters
- schedule data refresh and update the data in first point every 15 minutes in power BI workspace ( here I get that my data source is not supported for refresh error - " This dataset includes a dynamic data source. ")
- I have read this blog post about “Faking Out” Web.Contents (http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/), but I cannot seem to get it working.
Below is my query, any help would be much appreciated:
let
EntitiesPerPage = 500,
Limit="&limit=" & Text.From(EntitiesPerPage),
Url = "https://*******.********.com/api/1/rest/public/runtime/dev_org2?org_wide=true&last_hours=24" & Limit,
GetJson = (Url) =>
let
RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = Url & "&offset=0",
Json = GetJson(Url),
Count = Json[#"response_map"],
Count1 = Count[#"total"]
in
Count1,
GetPage = (Index) =>
let
//(option A)offset equal to previous row count
offset = "&offset=" & Text.From(Index * EntitiesPerPage),
//(option B)offset equal to page numer
//offset = "&offset=" & Text.From(Index),
Url = Url & offset,
Json = GetJson(Url),
Value = Json[#"response_map"],
Value1 = Value[#"entries"]
in
Value1,
EntityCount = GetEntityCount(),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"documents", "state_timestamp", "error_documents", "label", "path_id", "state", "create_time", "duration", "cc_label", "runtime_label"}, {"Column1.documents", "Column1.state_timestamp", "Column1.error_documents", "Column1.label", "Column1.path_id", "Column1.state", "Column1.create_time", "Column1.duration", "Column1.cc_label", "Column1.runtime_label"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.cc_label", "cc_label"}, {"Column1.create_time", "create_time"}, {"Column1.documents", "documents"}, {"Column1.duration", "duration"}, {"Column1.error_documents", "error_documents"}, {"Column1.label", "label"}, {"Column1.path_id", "path_id"}, {"Column1.state", "state"}, {"Column1.runtime_label", "runtime_label"}, {"Column1.state_timestamp", "state_timestamp"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"state_timestamp", type datetime}, {"create_time", type datetime}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [state_timestamp] >= RangeStart and [state_timestamp] < RangeEnd)
in
#"Filtered Rows"
Regards,
Aleksandar
