Forum Discussion
ZEPBCOB77
1 year agoNew Member
Power Query And API Loop to pull multiple pages
Hi, I have tried a few different things and cannot seem to get it to work . I want to pulll 10-20 pages of data from a FINVIZ Screener and I can only get the first page to pull . I am using a blan...
- 11 months ago
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 CombinePagesNotes:
- Data source settings that worked for me in testing above:
- 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.
- 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.
- 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)
- 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
- Data source settings that worked for me in testing above:
v-venuppu
Community Support
11 months agoHi ZEPBCOB77 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you MarkLaf lbendlin for the prompt response.
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.
Thank you.