Meetup Data Source for Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-06-01_20h53_32

There are many SaaS (Software as a Service) data sources for Power BI, such as Mailchimp, SalesForce, and etc. However I haven’t found anything for Meetup. Meetup on the other hand is a data source that event organizers such as myself use mostly. In this post I will be using Meetup API to connect from Power BI Desktop and read the JSON output of Meetup API to build some nice visualizations in Power BI. You can read more about Power Query in Power BI online book from Rookie to Rock Star.

About Meetup

If you haven’t heard about Meetup so far; it is a platform for scheduling in-person events. As an organizer I announce meetings for my Meetup group (such as New Zealand BI user group), and Meetup helps with the registration for the event. As an audience I can join to groups in my area of interest and be informed about their upcoming meetings and RSVP those to attend or not attend. Meetup website itself gives some useful information to organizers, however as a Power BI geek I would love to dig into its data with a better tool.

2016-06-01_20h52_17

Fortunately Meetup has a set of API restful services which returns output as JSON format.

2016-06-01_20h53_32

Restful service means there are some URLs that I can browse and it gives me a response in JSON format. First of all I need to use my API key. API key would be different for anyone, and sorry I cannot share my API key with you, that’s why it is blurred in screenshot below. You can access your API key from here.

2016-06-01_20h59_21

Now I can call each service with this key.  Fortunately there is a good documentation on each service, input parameters, and output result set here. As an example if I want to get list of all meetup events in the past for my group, I can use V2 Events service with input parameters such as URL name of the group, and status to be “past” (means only fetch past meetings);

Note that group_urlname of my Meetup group can be easily found on my group web url:

2016-06-01_21h05_15

as per screenshot above my group url name is: New-Zealand-Business-Intelligence-User-Group

and you have to use your own API Key instead of XYZ in URL above.

So if I browse URL above it gives me a JSON result. JSON is a format for data which usually doesn’t have enter or space between fields, so as a result I would see a big text result like this:

2016-06-01_21h09_48

Get Data with Power BI

Now let’s browse URL above in Power BI and Power Query to see what can I do with it. I open a Power BI Desktop file, and Get Data from Web. and I enter URL above there (with my own API key obviously);

2016-06-01_21h12_05

Result of this will show me a record in Power Query editor window with results and meta columns. You can also see in screenshot below that Power Query uses JSON.Document and Web.Contents to get JSON result from a web URL.

2016-06-01_21h13_26

Now click on the results section List to expand that. This will drill down into the list and you will see a list with multiple items in the next step. This list is result set in our JSON data. the result set of the URL we run returns list of events. That means this list is list of events, and each row in this list is an event. As you can see I have a record in each row. I can convert the main list to table with first highlighting the whole column, and then click on To Table option from Transform menu in Power Query.

2016-06-01_21h20_17

When I click on To Table, I will see To Table dialog box that asks for two configuration options, first one for delimiter, and second one for handling extra columns.

2016-06-01_21h23_59

I don’t do any changes, and click on OK with default values. Result now would be a table with single column, which has a record in each row. I can see the Expand button on my column1 header.

2016-06-01_21h25_28

Click on expand button as mentioned in screenshot above, and it will list all columns in records and asks me to choose those I want.

2016-06-01_21h27_36

Well let’s bring them all to see what we have, so I leave it with select all and click on OK. Here is my result set loaded in Power Query now:

2016-06-01_21h29_42

Now I have event information such as Name, Description, venue and other fields. Some values are a record by themselves, such as Venue and Rating. I can expand them if I want to. Let’s leave it as is for now. I’ve loaded my first service result into Power Query. Now one important thing before any other step is to check time value. Because I want to do date/time analysis for my events, so it is important to see if I have my time value fetched. in screenshot below it shows my Time column as a numeric value.

2016-06-01_21h33_39

TimeStamp to Date Time

In the documentation of this service in Meetup mentioned that Time column is:

time = UTC start time of the event, in milliseconds since the epoch

That means it is timestamp formatted. Timestamp value is number of seconds from epoch which is 1970-01-01 00:00:00. I have previously written about how to change timestamp value to date time and it is fairly easy with adding seconds to it. However for this case our value is not seconds, it is milliseconds so I have to first divide it by 1000.

2016-06-01_21h39_58

This makes my number smaller now, and I can now feed it into adding a custom column as Event Time as below;

2016-06-01_21h42_32

If you want more information about this conversion read my blog post here. Now this will give me a date time value as below:

2016-06-01_21h44_10

Please note that times are UTC times, We never have meetings 4 AM! I won’t be changing UTC time for now, because I want more Month analysis and this is fine for that analysis.

Visualization

Let’s visualize what we’ve got so far. First step I have to make sure the data type of my column in Power BI model loaded as Date Time. I can go to Data Tab, click on my Event Time column and under Modeling tab check the data type, and if it is text or anything else, change it to Date/Time

2016-06-01_21h51_57

a warning message will be displayed for reports that already using this field, as I don’t have any report elements yet, I’ll confirm and continue.

I also have to make sure that yes_rsvp_count column (which is showing number of audience for each session) set as data type whole number. If it is not the case, then I change it to be whole number as below.

2016-06-01_21h57_50

Let’s build charts now. I start by an Area Chart showing Event Time as Axis (without hierarchy), and yes_rsvp_count as values

2016-06-01_21h59_41

I also apply some formatting and build a chart like this:

2016-06-01_22h00_55

Well, it shows our group is performing well, number of audience raised from 10 to 99! and trend line shows that we are progressing better and better. I can build some other visualizations such as bar chart with name of events and number of RSVP yes as below;

2016-06-01_22h04_17

I can see the very first event that had more than 50 audience was the event that my friend Ken Puls Excel MVP from Canada came here to speak in our user group, which was kind of a start into our popular events afterwards.

Adding More Data

I can add more data to build more meaningful insight out of it. For example I can fetch Members information to show their locations (city and country) on the map. For this I use Members API service which I can call as below;

2016-06-01_22h26_44

This time I haven’t explained step by step, but process is similar to what we’ve done for Events, so I skip this part. Now I want to create a custom column concatenated of city and country as below;

2016-06-01_22h33_34

I then will be using this new City Full column because it gives me more reliable map location than the city itself (because a city with same name might be part of two different countries).

RSVP Data

RSVP data for each event is really useful for me, because I can understand who attend in most of events, and then contact them and thanks them for their commitment to the group. RSVP data can be fetched for each event. So I have to check it for each event ID that what is the RSVP for it. Here is RSVP service URL:

Note that you have to use your own event_id for the URL above.

I start by fetching result of above URL into Power Query; and here is what I get after expanding the JSON list and records;

2016-06-01_22h42_06

Note that I’ve also expanded Members column to get Member ID, this is what I would use later to join to Members query. I also have a column for response which says if this member responded yes to the RSVP or not.  I name this query as RSVP.

Add a Parameter and Create Function

Query above is only for one event, but I am looking for data for every event. So I have to create a function for getting RSVP result and apply that on every row in Events query. I create a duplicate version of Events, and remove everything and keep only column1.id (which is event id).

2016-06-01_22h46_13

Now I create a parameter to make my RSVP query parametric. Click on Manage Parameters, and choose New Parameter.

2016-06-01_22h47_37

I create a parameter with name EventID and default value as one of my event ids;

2016-06-01_22h49_51

Now I go to advanced editor tab of my RSVP query, and change the script to use EventID variable as below;

Now that my query works with parameter, I can simply right click on it, and Create Function.

2016-06-01_22h53_20

I name the function as GetRSVP and it get EventID as input parameter

2016-06-01_22h53_58

Now I can go to my copy of Events query which named Events(2), and add a custom function that calls GetRSVP as below

2016-06-01_22h55_20

Result set would be a column with tables in each row, which I can expand to get RSVP columns there

2016-06-01_22h57_36

Note that you may hit the privacy configuration warning and errors at this step; because this is just for test. I went to Files -> Options and Settings -> Option, and change privacy setting to ignore ;) This is not best practice obviously, you have to set it up appropriate for production environment, but here is just a demo and test, so let’s have fun.

2016-06-02_15h47_46

I rename this query as RSVPs, and Close and Load data into Power BI. I’ll create relationship as below;

Events and RSVPs= column1.id from Events, Custom.Column1.event.id from RSVPs

RSVPs and Members= Custom.Column1.member.member_id from RSVPs, Column1.id from Members

2016-06-01_23h03_50

I can now visualize members by their geo location city information in a map

2016-06-01_23h04_37

It is interesting that we have some members in our user group in other continents :) However the majority are in Auckland, New Zealand as expected. I can also build another bar chart for members who RSVPd yes mostly. Here is my final visualization with some formatting:

2016-06-01_23h06_41

I can still dig into more details with adding more data into my Power BI model and visualization, but I leave it to you now from here to see how far you can go with it. Have fun with Power BI and Meetup API.

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

9 Comments

  • Very cool, Reza. I’m not sure I’d have had the patience to read the documentation to work this out, so this was VERY helpful.

    I noticed the utc_offset is in milliseconds as well, rather than adjust any of the columns in individual steps, I just went right to a custom column, ending up with this:

    =#datetime(1970,1,1,0,0,0)+#duration(0,[utc_offset]/1000/60/60,0,[time]/1000)

    This gets the utc_offset back to hours by dividing it by 1000 millseconds per sec, 60 seconds per minutes and 60 minutes per hour. Works very nicely! :)

  • Hi Reza,

    Great post about how to set this up. It is actually something that I have been playing with for a little while. However, there is one thing that I haven’t yet been able to work out – refresh on app.powerbi.com.

    Have you been able to configure refresh (scheduled or on demand)?. If so, what’s the trick?
    Thanks!

    • Hi Luke,
      Thanks for your feedback.
      Refresh on Power BI website only works with web data source if the URL is not dynamic. in most of my example here the URL is static. However when I query RSVP result set, the URL resolved dynamically for each event. So that means this sample cannot be refreshed on Power BI website. Short answer; I can refresh it in Power BI if I don’t get RSVP section of this example.

      Cheers,
      Reza

  • Hello! Thank you for your “book”, interesting to read.
    You’ve forgotten to hide your meetup api key in script sample of RSVP query with parameter.

    • Thanks Devin,
      Pleasure to see your comment here :)
      Not sure if I understand the issue correctly. Did I mistakenly put something in the post somewhere?
      Cheers
      Reza

Leave a Reply to Ken Puls Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">