close

How To Get Data From Power BI Dataset Via An API

Posted by Kevin Booth on Apr, 23, 2021 03:04

An API or application programming interface is a set of protocols that allows software components to interact over the internet, in a coordinated way. The unit providing a service is called a server and the unit requesting the service is called the client. Let’s scratch the surface of Web API’s and its basic working before we get into how to use it to get data from Power BI in API.

Web API

Most of the web-based services make use of REST API. REST stands for Representational State Transfer, which was introduced by Roy Fielding as a research paper, in the year 2000. The client requests the server by sending a GET request to a URL and the server sends an XML, JSON, or similar file in response. Different languages have different built-in parsers, to extract data from JSON or XML files.

Methods of REST API

  • GET (To read a resource)
  • POST (To create a resource)
  • PUT (To put a resource)
  • DELETE (To delete a resource)

What Are The Principles Of REST API?

Here are the 6 principles of REST API are:

  1. Stateless Protocol
  2. Client-Server architecture
  3. Uniformity
  4. Uses Cache
  5. Multi-Layered System
  6. Code on demand

1) Stateless: Each request an independent entity for the server and it has no memory of the previous request.

2) Client-Server: Logic and GUI is separated to make the architecture more efficient. It can easily be scaled as per requirement.

3) Uniform Interface: REST has a major constraint that ensures uniformity among multiple clients.

  • Identifying the Resource
  • Manipulate resource using representational state
  • Self-explanatory messages

4) Cacheable: To speed up the process and avoid fetching the data from DB frequently, the app is made cacheable and the data can be reused.

5) Layered System: Multi-layered system puts restriction on each layer as to how much it can access. It automatically checks security concerns and leakage of privilege.

REST API is just the URL which is made up of several parts like this:

  • The Endpoint: It is the target URL which is going to give us the result when called for.
  • The Method: Depending on what do you want to do with the API call, this method can be adjusted. All the available methods have already been mentioned above.
  • The Headers: It contains routine info like sender, receiver, encryption used, authentication types and other details which we don’t need to worry about.
  • The Data (Body): This is the actual body part which we needed in the first place. It can be in JSON format, XML format, or another format.

The process to get data in Power BI via an API

Step 1:

Open the Power BI desktop. Click on the “Get Data” button and select the “Web” option as shown in the below screenshot.

Process to get data in Power BI

Step 2:

Click on the “Connect” button. A new window will pop up and you will see two options. (Basic and Advanced)

Get Data From web

Step 3:

You must enter the URL of API which returns the data you want to fetch. Fill in the URL and click on OK. 

Power BI will automatically arrange the results fetched by calling in a key-value pair in the form of rows. You can also use the advance option if you need.

Conclusion

In this tutorial, you saw how we can use API to fetch data from a remote server into the Power BI. You can either set up your own API to test it or use a free API on the internet. POSTMAN is software that can help you test your API service. You can read about more Power BI features that help you with Big Data.

EPC Group Microsoft Gold Partners

You can also reach out to EPCGroup for help with Microsoft Business Intelligence. We have more than 70+ in-house Power BI Developers that can help from Power BI training to full implementation of BI solutions in your organization.