Need help with power Query to schedule dynamic refresh of data and keep old data

%3CLINGO-SUB%20id%3D%22lingo-sub-2020170%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20power%20Query%20to%20schedule%20dynamic%20refresh%20of%20data%20and%20keep%20old%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2020170%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EI%20need%20to%20keep%20the%20data%20for%20the%20past%2024%20hours%20that%20I%20refresh%20in%20power%20BI%20desctop%20(thats%20how%20I%20model%20my%20report%20and%20dataset)%26nbsp%3B%3C%2FLI%3E%3CLI%3EI%20have%20premium%20and%20pro%20account%3C%2FLI%3E%3CLI%3EI%20have%20set%26nbsp%3BRangeStart%26nbsp%3B%20and%26nbsp%3BRangeEnd%20parameters%3C%2FLI%3E%3CLI%3Eschedule%20data%20refresh%20and%20update%20the%20data%20in%20first%20point%20every%2015%20minutes%20in%20power%20BI%20workspace%20(%20here%20I%20get%20that%20my%20data%20source%20is%20not%20supported%20for%20refresh%20error%20-%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%20This%20dataset%20includes%20a%20dynamic%20data%20source.%20%22%3C%2FSPAN%3E)%26nbsp%3B%3C%2FLI%3E%3CLI%3EI%20have%20read%20this%20blog%20post%20about%26nbsp%3B%E2%80%9CFaking%20Out%E2%80%9D%20Web.Contents%20(%3CA%20href%3D%22http%3A%2F%2Fblog.datainspirations.com%2F2018%2F02%2F17%2Fdynamic-web-contents-and-power-bi-refresh-errors%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttp%3A%2F%2Fblog.datainspirations.com%2F2018%2F02%2F17%2Fdynamic-web-contents-and-power-bi-refresh-errors%2F%3C%2FA%3E)%2C%20but%20I%20cannot%20seem%20to%20get%20it%20working.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EBelow%20is%20my%20query%2C%20any%20help%20would%20be%20much%20appreciated%3A%3C%2FP%3E%3CP%3Elet%3CBR%20%2F%3EEntitiesPerPage%20%3D%20500%2C%3CBR%20%2F%3ELimit%3D%22%26amp%3Blimit%3D%22%20%26amp%3B%20Text.From(EntitiesPerPage)%2C%3CBR%20%2F%3EUrl%20%3D%20%22https%3A%2F%2F*******.********.com%2Fapi%2F1%2Frest%2Fpublic%2Fruntime%2Fdev_org2%3Forg_wide%3Dtrue%26amp%3Blast_hours%3D24%22%20%26amp%3B%20Limit%2C%3CBR%20%2F%3EGetJson%20%3D%20(Url)%20%3D%26gt%3B%3CBR%20%2F%3Elet%3CBR%20%2F%3ERawData%20%3D%20Web.Contents(Url)%2C%3CBR%20%2F%3EJson%20%3D%20Json.Document(RawData)%3CBR%20%2F%3Ein%20Json%2C%3CBR%20%2F%3E%3CBR%20%2F%3EGetEntityCount%20%3D%20()%20%3D%26gt%3B%3CBR%20%2F%3Elet%20Url%20%3D%20Url%20%26amp%3B%20%22%26amp%3Boffset%3D0%22%2C%3CBR%20%2F%3EJson%20%3D%20GetJson(Url)%2C%3CBR%20%2F%3ECount%20%3D%20Json%5B%23%22response_map%22%5D%2C%3CBR%20%2F%3ECount1%20%3D%20Count%5B%23%22total%22%5D%3CBR%20%2F%3Ein%3CBR%20%2F%3ECount1%2C%3C%2FP%3E%3CP%3EGetPage%20%3D%20(Index)%20%3D%26gt%3B%3CBR%20%2F%3Elet%3CBR%20%2F%3E%2F%2F(option%20A)offset%20equal%20to%20previous%20row%20count%3CBR%20%2F%3Eoffset%20%3D%20%22%26amp%3Boffset%3D%22%20%26amp%3B%20Text.From(Index%20*%20EntitiesPerPage)%2C%3CBR%20%2F%3E%2F%2F(option%20B)offset%20equal%20to%20page%20numer%3CBR%20%2F%3E%2F%2Foffset%20%3D%20%22%26amp%3Boffset%3D%22%20%26amp%3B%20Text.From(Index)%2C%3CBR%20%2F%3EUrl%20%3D%20Url%20%26amp%3B%20offset%2C%3CBR%20%2F%3EJson%20%3D%20GetJson(Url)%2C%3CBR%20%2F%3EValue%20%3D%20Json%5B%23%22response_map%22%5D%2C%3CBR%20%2F%3EValue1%20%3D%20Value%5B%23%22entries%22%5D%3CBR%20%2F%3Ein%3CBR%20%2F%3EValue1%2C%3C%2FP%3E%3CP%3EEntityCount%20%3D%20GetEntityCount()%2C%3CBR%20%2F%3EPageCount%20%3D%20Number.RoundUp(EntityCount%20%2F%20EntitiesPerPage)%2C%3CBR%20%2F%3EPageIndices%20%3D%20%7B%200%20..%20PageCount%20-%201%20%7D%2C%3CBR%20%2F%3EPages%20%3D%20List.Transform(PageIndices%2C%20each%20GetPage(_))%2C%3CBR%20%2F%3EEntities%20%3D%20List.Union(Pages)%2C%3CBR%20%2F%3ETable%20%3D%20Table.FromList(Entities%2C%20Splitter.SplitByNothing()%2C%20null%2C%20null%2C%20ExtraValues.Error)%2C%3CBR%20%2F%3E%23%22Expanded%20Column1%22%20%3D%20Table.ExpandRecordColumn(Table%2C%20%22Column1%22%2C%20%7B%22documents%22%2C%20%22state_timestamp%22%2C%20%22error_documents%22%2C%20%22label%22%2C%20%22path_id%22%2C%20%22state%22%2C%20%22create_time%22%2C%20%22duration%22%2C%20%22cc_label%22%2C%20%22runtime_label%22%7D%2C%20%7B%22Column1.documents%22%2C%20%22Column1.state_timestamp%22%2C%20%22Column1.error_documents%22%2C%20%22Column1.label%22%2C%20%22Column1.path_id%22%2C%20%22Column1.state%22%2C%20%22Column1.create_time%22%2C%20%22Column1.duration%22%2C%20%22Column1.cc_label%22%2C%20%22Column1.runtime_label%22%7D)%2C%3CBR%20%2F%3E%23%22Renamed%20Columns%22%20%3D%20Table.RenameColumns(%23%22Expanded%20Column1%22%2C%7B%7B%22Column1.cc_label%22%2C%20%22cc_label%22%7D%2C%20%7B%22Column1.create_time%22%2C%20%22create_time%22%7D%2C%20%7B%22Column1.documents%22%2C%20%22documents%22%7D%2C%20%7B%22Column1.duration%22%2C%20%22duration%22%7D%2C%20%7B%22Column1.error_documents%22%2C%20%22error_documents%22%7D%2C%20%7B%22Column1.label%22%2C%20%22label%22%7D%2C%20%7B%22Column1.path_id%22%2C%20%22path_id%22%7D%2C%20%7B%22Column1.state%22%2C%20%22state%22%7D%2C%20%7B%22Column1.runtime_label%22%2C%20%22runtime_label%22%7D%2C%20%7B%22Column1.state_timestamp%22%2C%20%22state_timestamp%22%7D%7D)%2C%3CBR%20%2F%3E%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%23%22Renamed%20Columns%22%2C%7B%7B%22state_timestamp%22%2C%20type%20datetime%7D%2C%20%7B%22create_time%22%2C%20type%20datetime%7D%7D)%2C%3CBR%20%2F%3E%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(%23%22Changed%20Type%22%2C%20each%20%5Bstate_timestamp%5D%20%26gt%3B%3D%20RangeStart%20and%20%5Bstate_timestamp%5D%20%26lt%3B%20RangeEnd)%3CBR%20%2F%3Ein%3CBR%20%2F%3E%23%22Filtered%20Rows%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EAleksandar%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2020170%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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

0 Replies