Tools and Notebook

DataStore API tool integrations and Python notebook

Integration with Tools

The DataStore API can be integrated into various tools to enable automated, repeatable data retrieval and analysis workflows. See examples below.

Don’t see your tool listed but want help? Let us know by emailing [e-mail]!

Power BI

Follow this section to connect HDX’s DataStore API to PowerBI as a data source. This can also be used as a Power Query in other Microsoft tools, such as Fabric.

1. Open PowerBI Desktop. Within the PowerBI Desktop application (not the web version), go to Home → Get data. Select “Blank Query”. This will open the Power Query Editor window.

   1. 
2. Open Blank Query. Click “Advanced Editor” in the top rail.

   1. 
3. Paste PowerQuery with Custom Fields. In the editor, copy and paste this PowerQuery M script. You must replace the following with your own information.

   1. BaseUrl (Endpoint)

   2. ResourceID

   3. ApiToken (Your own API token)

   4. 
4. Set the connection to Anonymous. If prompted for credentials, choose “Anonymous”. If no prompt appears but there is an error, ensure you go to File → Options and settings → Data source settings. Find the entry for your data and clear permissions. Re-run the query which should prompt you to choose Anonymous.

5. Apply the Query. Once the preview loads in the Power Query Editor, click “Close & Apply”. Power BI should fetch the full dataset using the pagination logic and filters.

   1. 
6. [Optional] Add a Filter. If you would like to apply a filter, you need to update the PowerQuery to include the filter in the config. See example here for an organization filter to IOM. Note this will need to be configured per the resource schema.

   1. OrgFilter (Param filter)

Python Notebook

We provide a ready-to-run Python ipynb notebook showing how to query the DataStore API (schema information, native and SQL) for Python scripts and pipelines. You can open it directly in your browser via GitHub and test queries without needing to install anything locally.

Open the Python notebook here.

Important:

• You must provide your own API token in the notebook.

• Never commit your token or share it publicly! Treat it like a password.

The notebook covers:

• Searching for resources using the HDX CKAN API

• Making a minimal native query “probe” call with limit=1

• Use SQL query to join two resources with a common column

• Filter in both native and SQL queries

• Fetching data with filters and pagination