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.
