How to retrieve all pages without knowing the number of pages from API as a web data source

[venusura483]

Hi Experts !

I'm trying to retrive all apges from the web API source but the below power Query is keep loading infinitely with null values.

fUsers:

(Page as number)=>

let

    Source = Json.Document(Web.Contents("https://api.apples.com/api/v1/users?page="&Number.ToText(Page), [Headers=[Authorization="Bearer Token"]])),

    #"Converted to Table" = Table.FromRecords({Source}),

    #"Expanded Items" = Table.ExpandListColumn(#"Converted to Table", "Items"),

    #"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"Id", "Username", "FirstName", "Surname"}, {"Items.Id", "Items.Username", "Items.FirstName", "Items.Surname"}),

    #"Expanded Pagination" = Table.ExpandRecordColumn(#"Expanded Items1", "Pagination", {"PageNumber", "PageSizeRequested", "ItemsCount", "HasMore"}, {"Pagination.PageNumber", "Pagination.PageSizeRequested", "Pagination.ItemsCount", "Pagination.HasMore"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Pagination",{{"Items.Id", type text}, {"Items.Username", type text}, {"Items.FirstName", type text}, {"Items.Surname", type text}, {"Pagination.PageNumber", Int64.Type}, {"Pagination.PageSizeRequested", Int64.Type}, {"Pagination.ItemsCount", Int64.Type}, {"Pagination.HasMore", type logical}})

in

    #"Changed Type"

Query 1:

let

    Source = List.Generate( ()=>

    [Result = if Table.IsEmpty(fUsers(1)) then null else fUsers(1), n=0],

    each [Result] <> null and not Table.IsEmpty([Result]),

    each [Result = if Table.IsEmpty(fUsers([n]+1)) then null else fUsers([n]+1), n=[n]+1],

    each [Result]

),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Items.Id", "Items.Username", "Items.FirstName", "Items.Surname", "Pagination.PageNumber", "Pagination.PageSizeRequested", "Pagination.ItemsCount", "Pagination.HasMore"}, {"Items.Id", "Items.Username", "Items.FirstName", "Items.Surname", "Pagination.PageNumber", "Pagination.PageSizeRequested", "Pagination.ItemsCount", "Pagination.HasMore"})

in

    #"Expanded Column1"

Any help would be much appriciated.

thank you

[lbendlin]

Refactor your code to run repeatedly until Pagination.HasMore is false.

[venusura483]

Thanks @lbendlin , Can you please help me to modify below code to run until Pagination.HasMore is false.

Query 1:

let

    Source = List.Generate( ()=>

    [Result = if Table.IsEmpty(fUsers(1)) then null else fUsers(1), n=0],

    each [Result] <> null and not Table.IsEmpty([Result]),

    each [Result = if Table.IsEmpty(fUsers([n]+1)) then null else fUsers([n]+1), n=[n]+1],

    each [Result]

),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Items.Id", "Items.Username", "Items.FirstName", "Items.Surname", "Pagination.PageNumber", "Pagination.PageSizeRequested", "Pagination.ItemsCount", "Pagination.HasMore"}, {"Items.Id", "Items.Username", "Items.FirstName", "Items.Surname", "Pagination.PageNumber", "Pagination.PageSizeRequested", "Pagination.ItemsCount", "Pagination.HasMore"})

in

    #"Expanded Column1"

[lbendlin]

You can use whatever approach you like. As you can imagine it is close to impossible to advise without having access to the API.

Handling Paginated REST API Results with Power Query | John Dalesandro