How to retrieve all data from a Paginated API to pbi report

Hi Anonymous

Please try this modified code.  I remove the part that returns null if url is invalid. 

let
    /*create two columns containing numbers from 1 to 100 representing the Row and Page numbers,
    replace these with your desired numbers */
    Source = Table.FromColumns({{1..100}, {1..100}}),
    
    //rename the generated columns
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Row"}, {"Column2", "Page"}}),

    //generate the web urls and fetch the data table,
    //the 0 in {0}[Data] represents table 1 of the webpage
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each  
        let 
            url = Web.Contents( "http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/" & Number.ToText([Row]) & "/" & Number.ToText([Page]),
            [ManualStatusHandling={404}]), //check if webpage returns 404 error
            GetMetadata = Value.Metadata(url),
            GetResponseStatus = GetMetadata[Response.Status]
            in if GetResponseStatus=404 then null else url{0}[Data] //returns null if GetResponseStatus is 404
            , type table)     
in
    #"Added Custom"

Please post a screenshot of the error similar with below: