Power Query And API Loop to pull multiple pages

All you need is to add r=start_row to your query. Here is a query with Offset and NumberOfPages parameters. I also incorporated parsing of the table - obviously this part can break easily if the underlying html gets changed in an update.

 

 

let
    // screener data parameters
    Param_View = "151",
    Param_Filter = "ta_rsi_os40",
    Param_Order = "ticker",
    
    // screener paging parameters
    Param_Offset = 2,
    Param_NumberOfPages = 4,

    // screener constants - do not change
    Constant_PageSize = 20,

    // query to get pages
    GetPages = List.Generate(
        ()=>0,
        each _ < Param_NumberOfPages,
        each _ + 1,
        each [
            get = Web.Contents(
                "https://finviz.com/screener.ashx",
                [
                    Query = [
                        v=Param_View,
                        f=Param_Filter,
                        o=Param_Order,
                        r=Text.From( 
                            Param_Offset * Constant_PageSize 
                            + _ * Constant_PageSize + 1 
                        )
                    ]
                ]
            ),
            // convert binary to html text for parsing
            html = Text.FromBinary( get ),
            // parse html into tabular data
            // this relies on a particular html structure and is thus brittle
            screener_headers = Html.Table( 
                html, {{"Headers","*"}}, 
                [RowSelector="table.screener_table > thead th"] 
            )[Headers],
            screener_fieldcount = List.Count(screener_headers),
            screener_rows = List.Split( 
                Html.Table( 
                    html, {{"Rows","*"}}, 
                    [RowSelector="table.screener_table tr td"] 
                )[Rows], 
                screener_fieldcount 
            ),
            screener_rowschema = Record.FromList( 
                List.Repeat({[Type=type text,Optional=false]},screener_fieldcount), 
                screener_headers 
            ),
            screener_tabletype = type table Type.ForRecord(screener_rowschema, false),
            screener_table = Table.FromRows(screener_rows,screener_tabletype)
        ] [screener_table]
    ),
    CombinePages = Table.Combine( GetPages )
in
    CombinePages

 

 

 

Notes:

1. Data source settings that worked for me in testing above:
   
2. Check the terms of service. They have their api behind a paywall, so I wouldn't be surprised if they had language that limited certain types of interactions (e.g. web scraping) on their free screener.
3. All data coming through html is going to be text. The above does not handle the type transformations as I assume these will change depending on view/columns.
4. The screener page includes the total number of records in the filter. You can parse this from html and use to set the exact number of pages needed to get all records rather than blindly guessing (if your goal is to get all records rather than top 200 for example)
   
5. There is a more optimal approach that would include querying the first page separately to get: total number of records, screener table headers. Then, you could use List.Generate approach I use above to get all subsequent pages while a) leveraging total number of records to get all records or prevent querying a bunch of blank tables if you go over max pages, and b) pass through the table schema rather than doing it for each query as we are now