Pagination from an API that doesn' tell you how many pages

[Gauteweb]

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?

[Anonymous]

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.

[Gauteweb]

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...