Function that Repeats or Loops Through API Call Data

[rlmeyer]

I am working through bringing in data through an API Call. The API allows for 100 records each call. In order to get the next round of data you need to supply the "end_cursor" value from the previous call.

In the function below, I manually added in each step and then took the output value to supply the source url based on the API_Call count. Screenshot below of the function getting invoked on the query.

I was hoping to create a loop that used the API_Call count and supplied the source value if API_Call = the query number or returned back to the function, and got then got the next end_cursor value and supplied that source value. 

Does anyone have any examples of how to run a function through a loop and use the output to feed the next loop.

(API_Call as number) =>
let
	url = "https://API_Example.com/api/rest/example/Data?first=100",

	url_0 = url,
    Source_0 = Json.Document(Web.Contents(url_0)),
    page_info_0 = Source_0[page_info],
    end_cursor_0 = page_info_0[end_cursor],

    url_1 = url&"&after="&end_cursor_0,
    Source_1 = Json.Document(Web.Contents(url_1)),
    page_info_1 = Source_1[page_info],
    end_cursor_1 = page_info_1[end_cursor],

    url_2 = url&"&after="&end_cursor_1,
    Source_2 = Json.Document(Web.Contents(url_2)),
    page_info_2 = Source_2[page_info],
    end_cursor_2 = page_info_2[end_cursor],

    url_3 = url&"&after="&end_cursor_2,
    Source_3 = Json.Document(Web.Contents(url_3)),
    page_info_3 = Source_3[page_info],
    end_cursor_3 = page_info_3[end_cursor],

    url_4 = url&"&after="&end_cursor_3,
    Source_4 = Json.Document(Web.Contents(url_4)),
    page_info_4 = Source_4[page_info],
    end_cursor_4 = page_info_4[end_cursor],

    output =
    if API_Call = 0
    then Source_0
    else if API_Call = 1
    then Source_1
    else if API_Call = 2
    then Source_2
    else if API_Call = 3
    then Source_3
    else if API_Call = 4
    then Source_4
    else null
in
    output

The end_cursors are all unqie strings of text (examples).

A5ZjIzZi02NjJiLTY3NWEtODRkZi1jZGI3MjA2RkYWQiXQ==
QyNmRhZi1kM2IzLWRlM2ItYWM5YS1hNDNzIyZDZlZjQiXQ==
RmZmEzMy0yYzhlLWY4MTUtZWRkYi1MzNkMTmMzljODAiXQ==
RiNWQ0OC05ZGFmLTM5MGItM1ZC0yOGYwMjEwMTcyNGUiXQ==
g0ZGY3Mi0wMDZhLTQ5NzEtOTgzZS1lODk4YmJjD2M2YiXQ==
U3NDBmOC1iMzlhLTRkYWUtOTdkOC0yMDkkz5ZGQxMGMiXQ==

[lbendlin · Accepted solution]

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.

[lbendlin · Accepted solution]

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.

[rlmeyer · Accepted solution]

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