This article is focused on APIs as a data source in Power BI feature. It answers the following questions:

1. What are APIs?
2. What is APIs as a data source in Power BI?
3. When should you use REST APIs as a data source in Power BI?
4. How can you use REST APIs as a data source in Power BI?
5. How can you configure Power BI scheduled Refresh with REST API as a data source?

It also provides the best practices and challenges related to APIs as a data source in Power BI. You can consider this article as a cheat sheet for this feature. It is a collection of the best tutorials. In addition, it includes my professional experience and tips using this feature.

What are APIs?

API stands for application programming interface, which is a set of definitions and protocols for building and integrating application software. The most common formats for API are JSON (JavaScript Object Notation) and XML (Extensible Markup Language). Check this informative link to know more about API technology.

What is the APIs data source in Power BI?

Power BI has different data source options. One of them is APIs, you can pull data from APIs and use it in your report. This feature is called the Web data source under the Other section in Power BI desktop. To know more about other data sources for Power BI, check this.

When should you use REST APIs data source in Power BI?

Let me tell you about a client case. The client has a Drupal (PHP) application with no license for SQL on their server to build ETL and SSIS. The client requested to build public Power BI reports with only a pro Power BI license. It would be an extra cost for them to add an SQL license since they are using MySQL and open-source CMS for their application.

However they have an APIs interface for web application data, in that case, the best option I had is using APIs as a data source in Power BI then I Published them to the web and set a refreshing schedule.

How can you use REST APIs as a data source in Power BI?

You should make some transformations on APIs data so that you can use it in Power BI. Using the transform data option is an essential step. The second essential step is to change records into a table. This is a very good step-by-step tutorial

How can you configure Power BI scheduled Refresh with REST API as a data source?

Power BI service provides a feature to keep reports synced with the latest data fetched from APIs. You can choose an hour and a minute to refresh the reports. This timeslot refresh reports daily on time with the timezone you chose. You can check the refresh history status. This is a very good step-by-step tutorial

Best Practice

1- To guarantee Data security and privacy. APIs should be HTTPS with an authentication key header.

2- If your APIs have the same base URL, it is better to use the parameters feature in Power BI. This tip is a time saver. If there is a different environment like the dev and production environment, you can change the base URL for the environment instead of all API URL.

You can apply the same tip if API has an ID in the URL, you can set ID as a parameter as well. Check this blog article section to know how to use the parameters with APIs in Power BI.

Challenges

Sometimes, APIs are paginated which means data can not be fetched with an only request. You should fetch data using the same API URL while looping on available pages number. This is a comprehensive tutorial for Paginated APIs with Power Query.

Let me know if you have questions related to API as the data source in Power BI.

What is 3Qs++?

3Qs are What, When, and How.

++ stands for best practices and challenges.