cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rlmeyer
Resolver I
Resolver I

Function that Repeats or Loops Through API Call Data

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==

 

rlmeyer_0-1645033357912.png

rlmeyer_1-1645033782569.png

 

 

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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.

View solution in original post

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

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors