SQLServerCentral Article

How to create a Power BI report using REST API data

,

Introduction

In this article, we will learn how to create a Power BI report using a REST API. REST API is an Application Program Interface that uses HTTP requests to get data. Usually, the data is sent in JSON format. For example, you can use REST API to get data from Facebook or update information in your Facebook account using REST API. REST API is used by several applications like Twitter, Azure, AWS, Shopify, etc.

We will provide a brief introduction to REST API, and once, we learn how to work with it, we will teach how to create a Power BI report using REST API data.

Audience

This article is oriented toward newbies in REST API and Power BI.

Requirements to create a Power BI report using REST API data

  1. First, you need to have Power BI Desktop installed.
  2. Secondly, internet access in the machine with Power BI installed.

REST API introduction

Basically, most of the services on the web include an API that you can use to read, and update data. For example, Zendesk, Zoho, Hubspot, Google Sheets, Google BigQuery, OneDrive, and Jira. You could also get the weather, statistics about COVID, sports, etc. using a REST API. In addition, the authentication methods may be different for each API and not all the APIs are supported in Power BI, however, some work and we could get nice reports using this information.

HTTP Methods in REST API

In a REST API there are 5 main verbs or methods to handle data.

HTTP MethodDescription
POSTIt is used to create new objects or send data.
GETReads or retrieves data.
PUTUpdates or replaces data.
PATCHUpdates or modifies data.
DELETEDeletes data.

A Basic REST API Example

First, we will try a simple example with the GET method. We will just send an HTTP request and we will be able to read data. In the browser, we will use the following HTTP request.

https://api.publicapis.org/categories

The result displayed by the HTTP is in JSON format.

{"count":51,"categories":["Animals","Anime","Anti-Malware","Art \u0026 Design","Authentication \u0026 Authorization","Blockchain","Books","Business","Calendar","Cloud Storage \u0026 File Sharing","Continuous Integration","Cryptocurrency","Currency Exchange","Data Validation","Development","Dictionaries","Documents \u0026 Productivity","Email","Entertainment","Environment","Events","Finance","Food \u0026 Drink","Games \u0026 Comics","Geocoding","Government","Health","Jobs","Machine Learning","Music","News","Open Data","Open Source Projects","Patent","Personality","Phone","Photography","Programming","Science \u0026 Math","Security","Shopping","Social","Sports \u0026 Fitness","Test Data","Text Analysis","Tracking","Transportation","URL Shorteners","Vehicle","Video","Weather"]}

The JSON is a JavaScript Object Notation which is a simple format to store and transport data. It is easier than XML and it is replacing XML to retrieve data. If you want to read the JSON data, you can use a JSON formatted. We will use jsonformatter.curiousconcept.com, which is a free tool online.

 

Copy and paste your JSON code there and press the process button.

json formatted

Finally, you will have a more legible JSON file with the categories of the REST API page.

json data

So, basically, to call the API, we use a GET request and look for the categories using a public API from api.publicapis.org.

JSON Syntax

First, we have the basic syntax for JSON which includes the open and close brackets. We then use the key and the value separated by a colon.

{"count":51}

In this example, count saves the total number of categories.

Secondly, followed by a comma, we have all the categories in an array. The categories are separated by commas.

"categories":[
"Animals",
"Anime",
"Anti-Malware",
"Art \u0026 Design",
"Authentication \u0026 Authorization",
"Blockchain",
"Books",
"Business",
"Calendar",
"Cloud Storage \u0026 File Sharing",
"Continuous Integration",
"Cryptocurrency",
"Currency Exchange",
"Data Validation",
"Development",
"Dictionaries",
"Documents \u0026 Productivity",
"Email",
"Entertainment",
"Environment",
"Events",
"Finance",
"Food \u0026 Drink",
"Games \u0026 Comics",
"Geocoding",
"Government",
"Health",
"Jobs",
"Machine Learning",
"Music",
"News",
"Open Data",
"Open Source Projects",
"Patent",
"Personality",
"Phone",
"Photography",
"Programming",
"Science \u0026 Math",
"Security",
"Shopping",
"Social",
"Sports \u0026 Fitness",
"Test Data",
"Text Analysis",
"Tracking",
"Transportation",
"URL Shorteners",
"Vehicle",
"Video",
"Weather"
]

How to get data Power BI report using REST API data

First, open Power BI Desktop and press Get Data.

get data Power BI report using REST API

Secondly, select the Web option.

get data Power BI report using REST API - Web source

Thirdly, select the basic option and write the REST API URL. In this case, we only need the Basic option because this REST API example does not require credentials. The Advanced option can be used if you need more parameters and information to authenticate.

Use the following URL: https://api.publicapis.org/categories

get data Power BI report using REST API - Basic option

Also, if you need Advanced options, you can press the Add header button and add some keys to the header.

Advanced option

In addition, you will be able to see the data in Power BI.

get data Power BI report using REST API - Preview data

Next, select the Close & Apply option.

get data Power BI report using REST API - save and apply changes

Finally, if everything is OK, in Fields you will be able to see the categories and the count fields that store the total number of categories.

Fields in Power BI

How to create a Power BI report using REST API data

First, in Power BI Visualizations, select a table.

Table in Power BI

Secondly, drag and drop the categories field in the table. You will have a table like this one:

Table with categories

Thirdly, drag and drop the card from Visualizations to the report.

Card in power BI

Finally, drag and drop the count field in the card.

Drag and drop fields to the cardConclusion to create a Power BI report using REST API data

In this article, we learned what is REST API, we learn to read a JSON file which is the usual format for REST API responses. Also, we learned how to connect to REST API using Power BI and we created a simple report.

Final report

Rate

5 (1)

Share

Share

Rate

5 (1)