How to call REST APIs and parse JSON with Power BI


Just finished this week’s Power BI community webinar: Data Preparation is the Keystone by Reza Rad and one of the questions asked was:

Can you use Power BI to call REST APIs and parse JSON?

As luck would have it i was actually playing with doing exactly that and promised to do a blog post on it. 

When I took the 400 line “getting started” sample from the Visual Studio documentation down 6 lines i didn’t think it could be made simpler.

The fact one of my readers were still confused showed there was still room for improvement!

The fact that Power BI takes this to 0 lines and renders the resulting JSON into a beautiful set of Visuals with a flexible data model I am pretty certain sets the standard for the easiest way to call REST APIs and parsing JSON.

For this sample I have decided to call  REST API’s close to my heart –the Power BI Uservoice data at https://ideas.powerbi.com

With most REST API samples; you start by enabling access …With Uservoice is done with Tokens assigned to to an Admin. (in the call below I have removed my token and replaced it with XXXXXXXXXXXXXX’s).

For more information about using Tokens with the Uservoice API please see:  https://developer.uservoice.com/docs/api/technical-details/.  To grant a Client Token https://YOURDOMAIN.uservoice.com/admin/settings/api.

http://powerbi.uservoice.com/api/v1/forums/1/suggestions.json?client=XXXXXXXXXXXXXXXXXXXXXX  (note this won’t run as it doesn’t have a valid token!)

At this point you can  execute the call directly in a browser….But just like my Visual Studio REST sample, just not very pretty or useful!

image

Now that we have access  comes the fun part – putting into Power BI!

1. Choose Get Data from the start screen or the Home Tab.

image

 

2.  Select the “Other” category and select Web

image

3.  Paste/type in the REST call you want to make.  

image

 

4.  Depending on the call you are making this may take a while and return different JSON schema’s.

image

 

 

5.  At this point you will to explore the schema.  You can expand the elements one at time and if it isn’t the data in you are interested in simply remove it from the query properties.

In this case it is a pretty straightforward schema. A record with the meta data about the data called “Response_data” (see image below) and the list of elements(columns and their corresponding rows-which is what we want!).

image

6.  Expanding out the Suggestions > List gives use the following….So close!

image

7. Right clicking on the “List” column heading you want to expand this list into a table

image

8.   At this point we just need to expand the columns to have the REST API feeding the data model directly with a format we can build reports on. 

image

 

9.  Pretty amazing how easy this was compared to writing the code to parse this!

image

 

10.   Of course now that i have this data constantly available to Power BI creating reports is a breeze!

image

 

(In a future post i will need to do show pagination which Uservoice requires and setting up data model relationships with other REST API calls)

Comments (3)

  1. iris says:

    Very nice article about REST api, I use http://jsonformatter.org for formatting and analyzing JSON data.

  2. Ev says:

    This is great! Looking forward to handle pagination.

  3. Stephen Walker says:

    Hi did you ever figure out how to use pagination to pull back all records when the web service has a limit of 2k, for ex?

Skip to main content