Looping Through API with Next Link Parameter

[alexmoller]

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!

[lbendlin · Accepted solution]

use try ... otherwise ... and return an empty list for the latter.

[alexmoller]

Thanks for your reply, I'm not sure I follow what you mean, could you explain in more detail?

[alexmoller]

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"} ]))]
)