Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I've been reading up on the advice for looping through a paginated API as a source (great resources here, here and here).
I have a slightly different case which I'm trying to deal with where the API I'm using will paginate to infinity producing tables with just the column headers and no data. So the error checking is not kicking in because the pages do have a table, and the looping continues for ever!
So what I need is a check to say that if the number of rows in the table is 1 (just the headers) then stop the looping.
The function i'm using is:
= (page as number) as table => let Source = Web.Page(Web.Contents("https://api.xxxx.com&max-results=10000&page-num=" & Number.ToText(page) & "&apikey=12345")), Source1 = Source{0}[Data]
in
Source1
And the table source is:
= List.Generate( () => [Page Number =1,Func=null], each (try _[Func])[HasError]=false, each [Page Number=_[Page Number]+1, Func=GetData([Page Number]+1)], each _[Func])
I've looked at using Count.Rows but I'm not sure how to build it into this. Any ideas greatfully received!
Solved! Go to Solution.
Just change the condition accordingly.
each [Page Number] = 0 or
My solution:
Function "Get JSON" (which actually returns a list)
let
Source = (page as number) as list =>
let
URL = Text.Combine({
"https://some.url&page=",
Number.ToText(page)
}),
Response = Web.Contents(
URL
),
Source = Json.Document(Response)
in
Source
in
Source
Query
let
Source = List.Combine(
List.Generate(
() => [page = 1, Func=#"Get JSON"(1)], // initial
each List.Count([Func]) > 0, // condition
each [
page = [page]+1,
Func = #"Get JSON"([page])
], // next
each [Func] // selector
)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
#"Converted to Table"
It starts calling the "Get JSON" function starting with page=1 and going as long as the function returns at least 1 item.
You may use the following condition to replace the try expression.
each [Page Number] = 1 or Table.RowCount([Func]) > 1,
Hi @v-chuncz-msft,
Many thanks for taking the time to respond 🙂
I modified the code to:
= List.Generate( () => [Page Number=0, Func=null], each [Page Number] = 1 or Table.RowCount([Func]) > 1, each [Page Number=_[Page Number]+1, Func=GetData([Page Number]+1)], each _[Func])
But it gives me the error message:
Expression.Error: We cannot convert the value null to type Table.
Details:
Value=
Type=Type
If I try changing the line to say...
() => [Page Number=0, Func=1],
...then I still get the same message.
Any additional thoughts? Many thanks for your advice.
Just change the condition accordingly.
each [Page Number] = 0 or
Ah so obvious! 🙂
Thank you for your help.
Any chance you can please provide the full code? (without the actual API). Having the same challenge as well.
Thanks
Hi
Any chance you can provide all the code? I'm having the same challenge as well just now.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |