Function that Repeats or Loops Through API Call Data

Generally you have two options

1. Use List.Accumulate or a recursive function to fetch all data at once (this requires you to lug the results around which may cause shortness of memory

2. Use your knowledge of the total size and List.Generate to only harvest the end_cursor for each page. Then you can create all the required URLs and fetch the data again simply by using Table.AddColumn . Most of the time you will benefit from your browser cache so the penalty for pulling the same data twice will be very small.

Thank you lbendlin for pointing me in the right direction. List.Generate is working!

For anyone who is looking into something similar, this resource was very helpful:

How to use List.Generate to make API Calls in Power Query M - Gorilla BI

Here is my query:

let
    URL = "https://API_Example.com/api/rest/example/Data?first=100",
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(Username & ":" & Password ), BinaryEncoding.Base64),
    Source = Json.Document(Web.Contents(URL, [Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials]])),
    GetEndCursors = List.Generate(
    () => [ Source = Source, end_cursor = Source[page_info][end_cursor], next_page = Source[page_info][has_next_page] ],
    each not List.IsEmpty ([Source][data]),
    each [Source = Fn_EndCursor( [end_cursor] ), end_cursor = Source[page_info][end_cursor], next_page = Source[page_info][has_next_page] ],
    each [Source][data]
),
    #"Converted to Table" = Table.FromList(GetEndCursors, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
in
    #"Expanded Column1"

here is my function 'Fn_EndCursor' used above:

(end_cursor) =>
let
    URL = "https://API_Example.com/api/rest/example/Data?first=100",
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(Username & ":" & Password ), BinaryEncoding.Base64),
    Source = Json.Document(Web.Contents(URL&"&after="&end_cursor, [Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials]]))
in
    Source