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.
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...
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 |
---|---|
107 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |