Calling REST APIs and Parsing JSON made simple with Power BI

[chass]

(written for the Power BI blog but sharing here early)

As part of the community it is only natural you will want to know what is going on around you. Meetup
makes creating and tracking your local events a breeze and their REST APIs enables creating reports on those meetings.   Unfortunately traditionally REST APIs required complex programing due to threading and paging with many “simple” samples requiring hundereds 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/ 

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/

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!

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.

5.  Select the “Other” category and select Web

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.

[mizzoudavis]

Is there any configuration of Desktop/Web versions of Power BI that will allow a user to to have a REST source refreshed automatically?

[jsquaredz]

Good question. Love an answer on this one.

[richardbennell]

Hi chass

You may have inadvertently included your key in the screenshot above.

Am l correct in understanding that you cannot automatically refresh these API in the power bi service? If so is there a mechanism to do this?

If this is possible can you please show how.

Thanks

[chass]

@Richard -thanks for the heads up! I went ahead and changed the Key

@All

Yes, You can set up the gateway to refresh REST API's.  If your datasource is listed as not being refreshable it will likely have to do with the way the parameters are being passed in...For instance many REST calls will require a quantity or page number and developers will typically default is to building that string (I do this!).  This will not be refreshable with the current implementation of PowerBI

[Anonymous]

Hi I am PowerBI DEV for IBM. Currently working on project where I need to import data from Splunk System using Rest API. Splunk System Rest API can be called using Curl Command. PowerBI is not supporting Curl command. Could you please advise me how can I use C# or Powershell in PowerBI? Thanks

[Anonymous]

@chass @richardbennell @all

I know it is Challange to pull data from Splunk. However there is Search Rest API in Splunk system that enables to access the data but it is using curl  two commands to call Rest API.

1) execute search (like exececuting agent job in sql)

2) then get result of search job.

There are other options like c# powershell avaialble but to call these Rest APIs .Could you please advise me how to run other programming langauges in PowerBI.

[Anonymous]

It would be great to see an example of using PowerQuery to hit the PowerBI REST API to surface info that isn't visible in the service web UI

[009co]

This post no longer has the screenshots