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.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Net;  
  5. using System.Net.Http;  
  6. using System.Web.Http;  
  7.   
  8. namespace webapi.Controllers  
  9. {  
  10.     public class CountriesController : ApiController  
  11.     {  
  12.         [HttpGet]  
  13.         [Route("api/getcountries")]  
  14.   
  15.         public List<string>GetallCountries()  
  16.         {  
  17.             List<string> countryobject = new List<string>();  
  18.   
  19.             countryobject.Add("Austria");  
  20.             countryobject.Add("Afghanistan");  
  21.             countryobject.Add("Bangladesh");  
  22.             countryobject.Add("canada");  
  23.             countryobject.Add("China");  
  24.             countryobject.Add("Cuba");  
  25.             countryobject.Add("Denmark");  
  26.             countryobject.Add("India");  
  27.             countryobject.Add("Iran");  
  28.             countryobject.Add("Iraq");  
  29.             countryobject.Add("Japan");  
  30.             countryobject.Add("Kenya");  
  31.             countryobject.Add("Libya");  
  32.             countryobject.Add("Mali");  
  33.             countryobject.Add("Namibia");  
  34.             countryobject.Add("Nepal");  
  35.             countryobject.Add("Norway");  
  36.             countryobject.Add("Oman");  
  37.             countryobject.Add("Peru");  
  38.             countryobject.Add("Poland");  
  39.             countryobject.Add("Philippines");  
  40.   
  41.   
  42.             return countryobject;  
  43.         }  
  44.     }  
  45. }  
Through postman, I've tested the API and it is working as expected. 
 
Call REST APIs And Parse JSON With Power BI
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.
 
Call REST APIs And Parse JSON With Power BI
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.
Call REST APIs And Parse JSON With Power BI
figure(3) 
Advanced
 
Call REST APIs And Parse JSON With Power BI
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.
 
Call REST APIs And Parse JSON With Power BI 
figure(5)
 
Step 3 - Selection Visualization
 
Select appropriate visualizations from the visualizations tab. I am selecting Table visualization. See below. 
 
Call REST APIs And Parse JSON With Power BI 
figure(6)
 
Please click on the icon which I marked in the above figure(6). After clicking that icon, we will get table visualization.
 
Call REST APIs And Parse JSON With Power BI
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. 
 
Call REST APIs And Parse JSON With Power BI 
figure(8)
 
By following the above steps, we've loaded data in a table. See below.
 
Call REST APIs And Parse JSON With Power BI
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.