Hi!
I am having trouble with parametrisation of this query. It still is considered as dynamic data and can not be refreshed via PBI portal.
Any help would be appreciated.
let
baseuri = "https://app.caflou.com/",
RelPath = "api/v1/00000/timesheets/",
headers = [Headers=[Authorization="AuthToken"]],
initReq = Json.Document(Web.Contents("https://app.caflou.com/", [RelativePath="api/v1/0000/timesheets/", Headers=[Authorization="AuthToken"]])),
initData = initReq[results],
initNumber = initReq[page],
gather = (data as list, uri) =>
let
//get new offset from active uri
newOffset = Json.Document(Web.Contents(uri, headers))[next_page],
//build new uri using the original uri so we dont append offsests
newBaseUri = "https://app.caflou.com/",
newUri = "api/v1/0000/timesheets?page=" & Text.BetweenDelimiters(newOffset, "page=", "&") &"&per=20" ,
//get new req & data
newReq = Json.Document(Web.Contents(baseuri,
[RelativePath = "api/v1/0000/timesheets?page=" & Text.BetweenDelimiters(newOffset, "page=", "&") &"&per=20",
Headers=[Authorization="AuthToken"]])),
newdata = newReq[results],
//add that data to rolling aggregate
data = List.Combine({data, newdata}),
//if theres no next page of data, return. if there is, call @gather again to get more data
check = if newReq[next_page] = null then data else @gather(data,baseuri & newUri)
in check,
//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
outputList = if initReq[next_page] = null then initData else gather(initData, baseuri & RelPath)
in
outputList
Thank you!
Kind regards,
Pavel
