API returning only 1000 results

[bonjourposte]

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_i... (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?

[v-shex-msft]

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

[bonjourposte]

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.