Pull API 10000 initial rows for each month.

To pull only the initial 10000 rows for each month, you can modify the API request by using the $where parameter to filter the data based on the created_date field. Here's an example query that fetches the first 10,000 rows for each month:

https://data.cityofnewyork.us/resource/wn58-xk44.json?$where=extract(month from created_date) <= 10 AND row_number <= 10000

This query fetches only the rows where the created_date month is less than or equal to October (i.e., the first 10 months of the year) and where the row number is less than or equal to 10,000.

You can modify the $where clause to fetch data for different months or change the row limit as needed.

In Power Query, you can modify the Web.Contents function to include the $where parameter like this:

let url = "https://data.cityofnewyork.us/resource/wn58-xk44.json?$where=extract(month from created_date) <= 10 AND row_number <= 10000", Source = Json.Document(Web.Contents(url)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ... in #"Changed Type2"

Replace the url variable with the modified API query. This should limit the data to the first 10,000 rows for each month.

Best regards, 

Isaac Chavarria 


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly