Forum Discussion
Looping Through API with Next Link Parameter
Hello All,
I am trying to loop through a Graph API endpoint, using the next link value present in the response to get the next paginated list of values.
I am using list.generate in order to do this. The only problem is I get an error on the last row in the table. I think this is because on the final api call - there is no next link value present. Is there a way to handle this?
I cannot remove the error from the query. Here is the code I am using:
let
token_uri = "https://login.microsoftonline.com/" & #"TenantID" & "/oauth2/v2.0/token",
resource="https://graph.microsoft.com",
tokenResponse = Json.Document(Web.Contents(token_uri,
[
Content = Text.ToBinary(Uri.BuildQueryString(
[
client_id = #"ClientID",
scope = "https://graph.microsoft.com/.default",
grant_type = "client_credentials",
client_secret = #"Secret"
]
)),
Headers = [Accept = "application/json"], ManualStatusHandling = {400}
])),
access_token = tokenResponse[access_token],
List = List.Generate( () =>
[URL = "https://graph.microsoft.com/v1.0/communications/callRecords",
Result = Json.Document(Web.Contents("https://graph.microsoft.com/v1.0/communications/callRecords", [ Headers = [ Authorization = "Bearer " & access_token ], ExcludedFromCacheKey = {"Authorization"} ] ))],
each [URL] <> null,
each [URL = [Result][#"@odata.nextLink"],
Result = Json.Document(Web.Contents(URL,[ Headers = [ Authorization = "Bearer " & access_token ], ExcludedFromCacheKey = {"Authorization"} ]))]
),
#"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"URL", "Result"}, {"URL", "Result"}),
#"Expanded Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Result", {"@odata.context", "@odata.nextLink", "value"}, {"@odata.context", "@odata.nextLink", "value"})
in
#"Expanded Result"
Here is the result:
Many Thanks in advance!
use try ... otherwise ... and return an empty list for the latter.
3 Replies
- lbendlinSuper User
use try ... otherwise ... and return an empty list for the latter.
- alexmollerFrequent Visitor
Thanks for your reply, I'm not sure I follow what you mean, could you explain in more detail?
- alexmollerFrequent Visitor
Ah my apologies, I have worked it out - I just needed to add this code:
= List.Generate( () =>
[URL = "https://graph.microsoft.com/v1.0/communications/callRecords",
Result = Json.Document(Web.Contents("https://graph.microsoft.com/v1.0/communications/callRecords", [ Headers = [ Authorization = "Bearer " & access_token ], ExcludedFromCacheKey = {"Authorization"} ] ))],
each [URL] <> null,
each [URL = try [Result][#"@odata.nextLink"] otherwise null,
Result = Json.Document(Web.Contents(URL,[ Headers = [ Authorization = "Bearer " & access_token ], ExcludedFromCacheKey = {"Authorization"} ]))]
)