Forum Discussion
API returning only 1000 results
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?
2 Replies
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
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.
