DirectQuery your custom REST API in Power BI

If you have a custom REST API and would want to Direct Query the API from Power BI instead of an importing data, this tutorial will guide you on how you can do that for any of your public or internal REST API’s.

To enable Direct Query for your REST API, the pieces of puzzle you need would be

1. A Custom ODBC Driver for your REST API
2. Power BI Data Connector that enables Direct Query

In this tutorial, I will be using REST API from NYC Open Data to demonstrate the Direct Query capabilities.

For a code-less solution, look at our new Autonomous REST Connector, which is designed to connect to any REST API easily. You can get started by following this tutorial.

1. If your Power BI is already running, save your work and Restart Power BI for the extension to show up.
2. In Power BI, click on Get Data -> All -> OpenAccessODBC. Choose it and you should now see a form as shown below where it will ask for DSN name. Below are my DSN Config(Find more about this in the tutorial for building ODBC driver) and Power BI Config. Also under Data Connectivity mode -> Choose DirectQuery
   
   
   
3. Click on OK and you should now see Authentication for the driver. As the API is open, it has no authentication. Click on Connect.
   
   
4. This will fetch all the tables from your custom ODBC driver you built for your REST API
   
   
   
   
5. Click on Load and you should now see that DirectQuery has been enabled for querying your REST API in Power BI footer as shown below
   
   
   

I hope this article helped you on how you can Direct Query your REST API using the ODBC driver built on Progress DataDirect OpenAccess SDK.  All the Progress DataDirect ODBC drivers and the drivers you can build using OpenAccess SDK are fully compliant to ODBC standards, and are internally certified to work with Power BI. Feel free to try these for your projects and if you have any questions or issues, feel free to contact us.