Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have this API I need to collect data from. It gives me maximum 100 records and ends with:
"has_more": true,
"url": "/content_sessions?expand=content&limit=100&order_by=-created_at",
"next_page_url": "/content_sessions?expand=content&limit=100&order_by=-created_at&starting_after=72e41626"
Here is the Power Query used:
let
Source = Json.Document(Web.Contents("https://api.userflow.com/", [Headers=[Authorization="Bearer XXXXXXXX"], RelativePath="/content_sessions?expand=content&limit=100&order_by=-created_at"])),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "object", "answers", "completed_at", "completed", "content_id", "content", "created_at", "group_id", "group", "is_preview", "last_activity_at", "launcher_activated", "progress", "user_id", "user", "version_id", "version"}, {"id", "object", "answers", "completed_at", "completed", "content_id", "content", "created_at", "group_id", "group", "is_preview", "last_activity_at", "launcher_activated", "progress", "user_id", "user", "version_id", "version"}),
#"Expanded content" = Table.ExpandRecordColumn(#"Expanded Column1", "content", {"id", "object", "created_at", "draft_version_id", "draft_version", "name", "published_version_id", "published_version", "type"}, {"id.1", "object.1", "created_at.1", "draft_version_id", "draft_version", "name", "published_version_id", "published_version", "type"})
in
#"Expanded content"
I've tried to follow many examples and the documentation, but haven't been able to get it to work. I understand that I need Power BI to keep fetching as long as the "has more" is "true", but how?
Hi @Gauteweb
You can try putting a page number parameter, and you can refer to the following link.
Handling Paginated REST API Results with Power Query | John Dalesandro
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
So I had a chat with chatGPT, and I think I'm getting closer.
let
url = "https://api.userflow.com/content_sessions?expand=content&limit=100&order_by=-created_at",
headers = [Authorization="Bearer XXXX"],
GetContentSessions = (url) =>
let
recursive = (url, sessions) =>
let
response = Json.Document(Web.Contents(url, [Headers=headers])),
more = response[has_more],
nextUrl = Text.Combine({"https://api.userflow.com/", response[next_page_url]}),
newSessions = sessions & response[content_sessions]
in
if more = true then
recursive(nextUrl, newSessions)
else
newSessions
in
recursive(url, {}),
Source = GetContentSessions(url)
in
Source
The error I'm getting now is: Expression.Error: The name 'recursive' wasn't recognized. Make sure it's spelled correctly.
...and I'm a bit stuck...