Calling REST APIs and Parsing JSON made simple with Power BI


Most development teams and software architecture is moving to loosely coupled service libraries based on REST APIs passing JSON objects.

Traditionally this is complex programing due to threading and paging with many “simple” samples requiring 100’s of lines of C#.

What most people don’t know is calling REST end points and parsing the resultant JSON is really easy in Power BI. 

This walk through will show you how to call and report on your club and user group activities using the Meetup REST API.

 

Step 1. Join the Meet up API group.   This is needed to get an access key.

http://www.meetup.com/meetup_api/ 

image

Step 2. Get a Meetup API key.

This is needed to both access the APIs and identify who “you” are to Meetup.   This can be done here: https://secure.meetup.com/meetup_api/key/

image

 

Step 3. Determine the API that gives you the data you want. 

In some cases you may need to call a couple API’s and create joins across multiple JSON files to give you the data you want; for Meetup it is pretty straight forward it is called Calendar under events.

http://www.meetup.com/meetup_api/docs/self/calendar/#list with the actual call being: https://api.meetup.com/self/calendar?key=XXXXXXXXXXXXXXXXXXXXXXXXXXX.

Note i have replaced my Meetup API key with X so this won’t run until you substitute the “X”s with your key.

At this point you can  execute the call directly in a browser….Just not very pretty or useful!

image

 

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

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

image

5.  Select the “Other” category and select Web

image

6.  Paste/type in the REST call you want to make.   In this case use the calendar API above: https://api.meetup.com/self/calendar?key=XXXXXXXXXXXXXXXXXXXXXXXXXXX.

-substituting the “X”s with your key.

image

7. This returns a series of JSON objects you need to tell Power BI how to parse…in this case into a table

image

8. With the events JSON placed into a table, all that is needed is to decide what columns you want.

At this point you should consider turning off “Use Original Column name as prefix” for this initial table creation.

image

9. For most JSON objects, you will need to expand additional (children) records into columns and in those cases it would make sense to keep the original column name as a prefix to avoid name collisions and disambiguate which column you are working with.

In the meetup example both Venue and Group columns would need to be further expanded into their constituent columns to used in any reports.

image

10. Adding a Visual or three in this case, on top of the data makes immediately valuable and a time saver!

 

image

Comments (0)