Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.