Call REST APIs And Parse JSON With Power BI

In this blog, we are going to discuss how to call REST APIs and parse JSON with PowerBI.

Introduction

 

In this blog, I will discuss how to call REST APIs and parse JSON with Power BI Desktop.

 

Step 1 - Create a Web API

 

First, we need to create an API. Create a Web API project in Visual Studio or use your existing Web API. Here is my example.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace webapi.Controllers
{
    public class CountriesController : ApiController
    {
        [HttpGet]
        [Route("api/getcountries")]

        public List<string>GetallCountries()
        {
            List<string> countryobject = new List<string>();

            countryobject.Add("Austria");
            countryobject.Add("Afghanistan");
            countryobject.Add("Bangladesh");
            countryobject.Add("canada");
            countryobject.Add("China");
            countryobject.Add("Cuba");
            countryobject.Add("Denmark");
            countryobject.Add("India");
            countryobject.Add("Iran");
            countryobject.Add("Iraq");
            countryobject.Add("Japan");
            countryobject.Add("Kenya");
            countryobject.Add("Libya");
            countryobject.Add("Mali");
            countryobject.Add("Namibia");
            countryobject.Add("Nepal");
            countryobject.Add("Norway");
            countryobject.Add("Oman");
            countryobject.Add("Peru");
            countryobject.Add("Poland");
            countryobject.Add("Philippines");


            return countryobject;
        }
    }
}

Through postman, I've tested the API and it is working as expected. 

 

Figure(1)

 

Step 2 - Run your Power BI desktop.

 

Run your Power BI Desktop.

 

Use the "Web" option for accessing data from an API. In the figure below, I have marked the GetData icon click on that and select Web option.

 

Figure(2)

 

After selecting the Web option, "From Web" window will appear. Please check below figure.

 

Basic

 

Using this option, we can access data from an API by providing an API URL only.

figure(3) 

Advanced

 

figure(4)

 

Using this option, we can access data via an API by providing the API URL. The difference here is, we can use query parameters, pass values at headers (when our API's are implemented with authentication) and we can mention content type, etc.

 

Let's access data from the API using Basic Option. Enter your API URL in textbox.

 

Click on the OK button. See figure(3).

 

Data is loaded from the API. See below.

 

 

figure(5)

 

Step 3 - Selection Visualization

 

Select appropriate visualizations from the visualizations tab. I am selecting Table visualization. See below. 

 

 

figure(6)

 

Please click on the icon which I marked in the above figure(6). After clicking that icon, we will get table visualization.

 

figure(7)

 

Now we need to load data for our visualization. For that click on the "get countries" checkbox, which is under the Fields tab when you expand it. 

 

 

figure(8)

 

By following the above steps, we've loaded data in a table. See below.

 

figure(9)

 

Summary

 

That's all.

 

We just used Power BI to get data from a Web API and generated a report. Using a similar method, you can build your API and use in Power BI.