Adjust paginated api call to enable refresh in PowerBI Service (M)

Question

I'm reworking a dashboard that is fine on PBI desktop but can't refresh on PBi service because it includes dynamic data sources (meta api). I'm trying to adjust the original code following Chris Webb's blog explanation to 'un-dynamise" the query (use of Query and RelativePath in Web.Contents). However, with my adjusted code, I'm only able to retrieve 60 days of data, whereas it was supposed to pull 12 months worth of data. I don't understand where the breaks happen.

Original Code:

let
initUntil = Number.ToText(todayInSeconds),
initSince = Number.ToText(@get30DaysBefore(todayInSeconds)),
baseUri = "https://graph.facebook.com/v15.0/ "&facebookPageID&"/insights?metric=metrics&period=day&since="&initSince&"&until="&initUntil&"&access_token="&metaPageToken,
initReq = Json.Document(Web.Contents(baseUri)),
initData = initReq[data],

gather = (data as list, uri as text , i as number) =>
let
newUri = Json.Document(Web.Contents(uri))[paging][previous],
newReq = Json.Document(Web.Contents(newUri)),
newData = newReq[data],
data = List.Combine({data, newData}),

check = if Table.Contains(Record.ToTable(newReq[paging]), [Name = "previous"]) = false then data else if i > 20 then data else @gather(data, newUri, i+1)
in
check,

outputList = if Table.Contains(Record.ToTable(initReq[paging]), [Name = "previous"]) = true then @gather(initData, baseUri, 1) else initData,
//End of relevant code

Adjusted Code (works partially)

let

initUntil = Number.ToText(todayInSeconds),
initSince = Number.ToText(@get30DaysBefore(todayInSeconds)),

url = "https://graph.facebook.com/v15.0/XXXXXXXXX/ ",
options = [RelativePath = "insights?",Query = [metric = metrics,period="day",since=initSince,until=initUntil,access_token=metaPageToken]],
initReq = Json.Document(Web.Contents(url, options)),

initData = initReq[data],

gather = (data as list, url, options , i as number) =>
let
newUri = Json.Document(Web.Contents(url, options))[paging][previous],
newReq = Json.Document(Web.Contents(newUri)),
newData = newReq[data],
data = List.Combine({data, newData}),

check = if Table.Contains(Record.ToTable(newReq[paging]), [Name = "previous"]) = false then data else if i > 20 then data else @gather(data, url, options, i+1)
in
check,

outputList = if Table.Contains(Record.ToTable(initReq[paging]), [Name = "previous"]) = true then @gather(initData, url, options, 1) else initData,
// End of code

Any help is welcome!

Answer 1

For documentation purposes, here is the solution I found to my issue (and it fully works) =>

let

initUntil = Number.ToText(todayInSeconds),
initSince = Number.ToText(@get30DaysBefore(todayInSeconds)),

url = "https://graph.facebook.com/",
StartingOptions = [RelativePath = "v15.0/xxxxxxxx/insights?metric=page_views_total,page_post_engagements,page_consumptions,page_consumptions_unique,page_impressions,page_impressions_unique,page_posts_impressions,page_posts_impressions_unique,page_fans,page_fan_adds&period=day",
Query = [since=initSince,until=initUntil,access_token=metaPageToken]],

initReq = Json.Document(Web.Contents("https://graph.facebook.com/", StartingOptions)),

initData = initReq[data],


gather = (data as list, QparmSince, QparmUntil, i as number) =>
   let     
       baseurl = "https://graph.facebook.com/",
       token = metaPageToken,
       StartingOptions =[RelativePath = "v15.0/xxxxxxxx/insights?metric=page_views_total,page_post_engagements,page_consumptions,page_consumptions_unique,page_impressions,page_impressions_unique,page_posts_impressions,page_posts_impressions_unique,page_fans,page_fan_adds&period=day",
Query = [since=QparmSince,until=QparmUntil,access_token=token]],
       newSince = Text.BetweenDelimiters(Text.From(Json.Document(Web.Contents(baseurl, StartingOptions))[paging][previous]), "&since=", "&"),
       newUntil = Text.BetweenDelimiters(Text.From(Json.Document(Web.Contents(baseurl, StartingOptions))[paging][previous]), "&until=", "&"),
       newOptions = [RelativePath = "v15.0/xxxxxxx/insights?metric=page_views_total,page_post_engagements,page_consumptions,page_consumptions_unique,page_impressions,page_impressions_unique,page_posts_impressions,page_posts_impressions_unique,page_fans,page_fan_adds&period=day",
Query = [since=newSince,until=newUntil,access_token=token]],
       newReq = Json.Document(Web.Contents(baseurl, newOptions)),
       newData = newReq[data],

       data = List.Combine({data, newData}),
    
       check = if Table.Contains(Record.ToTable(newReq[paging]), [Name = "previous"]) = false then data else if i > 20 then data else @gather(data, newSince, newUntil, i+1)
   in 
       check,

outputList = if Table.Contains(Record.ToTable(initReq[paging]), [Name = "previous"]) = true then @gather(initData, initSince, initUntil, 1) else initData,

The trick was to update within the @gather function the Since and Until query parameters by extracting them from the "Previous" paging of the Meta Api.