topic Re: API returning only 1000 results in Developer

API returning only 1000 results

bonjourposte — Thu, 16 Feb 2023 17:05:51 GMT

I'm using the SDMX API to call up census data, and there are 1600+ rows, but Power BI is only importing 1000.

This is the API:

http://api.statcan.gc.ca/census-recensement/profile/sdmx/rest/data/STC_CP,DF_FSA/A5..1.229.1

I'm following Curbal's tutorial on pagination, but the syntax is such that I don't know how or where to put the page # (it's different from her example)

For example, I've tried tacking on the following, and they don't work:

- https://api.statcan.gc.ca/census-recensement/profile/sdmx/rest/data/STC_CP,DF_FSA/A5..1.229.1/page=2

- https://api.statcan.gc.ca/census-recensement/profile/sdmx/rest/data/STC_CP,DF_FSA/A5..1.229.1/?page=2 (error message*)

- https://api.statcan.gc.ca/census-recensement/profile/sdmx/rest/data/STC_CP,DF_FSA/A5..1.229.1.page=2

- https://api.statcan.gc.ca/census-recensement/profile/sdmx/rest/data/STC_CP,DF_FSA/A5..1.229.1.?page=2 (error message*)

- http://api.statcan.gc.ca/census-recensement/profile/sdmx/rest/data/STC_CP,DF_FSA/A5..1.229.1.?page_id=2 (error message*)

- http://api.statcan.gc.ca/census-recensement/profile/sdmx/rest/data/STC_CP,DF_FSA/A5..1.229.1/?page_id=2 (error message*)

The error message is "unprocessable entity (422)"

Does anyone know the syntax to get the second page of data?

Re: API returning only 1000 results

Anonymous — Mon, 20 Feb 2023 05:56:22 GMT

Hi @bonjourposte,

I'd like to suggest you take a look at the following official document link with some helper functions to know how to handle pagination API:

Helper functions for M extensions for Power Query connectors - Power Query | Microsoft Learn

Regards,

Xiaoxin Sheng

Re: API returning only 1000 results

bonjourposte — Tue, 21 Feb 2023 19:37:31 GMT

Thanks, I contacted SDMX, and they said there is no page 2. The entire data WAS loading into Power BI, but I couldn't get it out into Excel. I eventually just manually merged every Code ID in a matrix visual, clicked the three dots, and exported to csv.