Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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==
Solved! Go to 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.
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
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
106 | |
97 | |
38 | |
32 |
User | Count |
---|---|
152 | |
122 | |
76 | |
74 | |
47 |