Issue with ‘Scheduled Refresh’ for api calls

Hi all: Good day. I have developed a PBI dashboard that have 3 api calls [2 of them get called dynamically passing ‘Id’] out of several data sources . It works in Desktop env and with manual data refresh in Service env. But issues happen as Publish team is trying to implement ‘scheduled refresh’. The team told me I need to change the code for the api connection to make it happen.

#1 ‘Notice’ api url:

https://api.xyz.com/natgas/events/v1/notices?priorities=critical&limit=50&offset=0&format=json

There is a max limit of 50 notices I can pull. I changed the code in Advanced Editor as shown below:

----------------------------------------

let GetData = () =>

    let

        pageSize = 50,

        sendRequest = (page)

            let

                Response = Web.Contents(

                    "https://api.xyz.com/natgas/events/v1/notices",

                  [

                      Query = [

                            priorities=”critical”,

limit= Number.ToText(pageSize),                          

                            format="json",

                            offset= Number.ToText(page * pageSize)

                        ]

                  ,

                      Headers = [

                          #"Gen-Api-Key"= "………." [not showing the key here]

                          ]

                  ]

                ),

                Data = Json.Document(Response)[data]

            in

                Data,

        Loop = (page as number, AccumData as list) =>

            let

                Data = Function.InvokeAfter(()=>sendRequest(page), #duration(0,0,0,2)),        

                Result =     

                    if List.Count(Data) < pageSize

                    or page >= 50

                    then Table.FromRecords(List.Combine({AccumData, Data}))

                    else @Loop(page + 1, List.Combine({AccumData, Data}))

            in

                Result

        in

            Loop(0, {})

in

    GetData

-----------------------------

 It works to pull the data and show up in a table visual as expected!

Now, I am not sure how to update code for the other api, ‘Notice details’.

I  get 50 notices [‘Id’ is the unique key] from api#1, then I need to get notice details for each notice. I call #2 ‘notice details’ api sending the ‘Id’ [dynamically created]

#2 ‘Notice details’ api url->

https://api.xyz.com/natgas/events/v1/notices/details?ids={ids}&format=json

-I created a parameter NoticeId and

-then a query, ‘query_notice_details’ using New source-> Web-> Advanced-> steps using ‘NoticeId’ parameter

-I then created a Function on the query->NoticeDetailsFunction

-I copied #1 data source and removed columns except ‘Id’, invoked the above function to get  ‘details_notices’ data source

‘query_notice_details’: code From advanced editor:

let

    Source = Json.Document(Web.Contents("https://api.xyz.com/natgas/events/v1/notices/details?ids=" & NoticeId & "&format=json", [Headers=[#"Gen-Api-Key"="……"]]))

in

    Source

-----------------------------------------------------------------

‘details_notices’: code

------------------------------------

let

    Source = notices_api(),

    #"Removed Columns" = Table.RemoveColumns(Source,{"pipelineName", "pipelineId", "priority", "type", "status", "subject", "postDate", "effDate", "endDate", "insertDate"}),

    #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "NoticeDetailsFunction", each NoticeDetailsFunction([id])),

    #"Expanded NoticeDetailsFunction" = Table.ExpandRecordColumn(#"Invoked Custom Function", "NoticeDetailsFunction", {"data"}, {"data"}),

    #"Expanded data" = Table.ExpandListColumn(#"Expanded NoticeDetailsFunction", "data"),

    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"pipelineName", "priority", "type", "subject", "body", "postDate", "effDate", "endDate"}, {"pipelineName", "priority", "type", "subject", "body", "postDate", "effDate", "endDate"})

in

    #"Expanded data1"

-----------------------------------------------

Not sure of the code change to make for invoking the function, NoticeDetailsFunction and iterate through for each NoticeDetailsFunction([id])?

---------------------------------------------------------------------------

I have already checked the link: https://eu001-sp.shell.com/sites/AAFAA3947/SitePages/SOURCES%20INFO%20-%20REST%20API.aspx

Sorry if I have overwhelmed you all the info! ☹ Many thanks in advance for your patience.