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.

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

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.

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);

https://api.meetup.com/2/events?status=past&group_urlname=New-Zealand-Business-Intelligence-User-Group&key=XYZ

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

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:

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);

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.

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.

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.

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.

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.

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:

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.

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.

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

#datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Column1.time])

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

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

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.

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

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

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;

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;

https://api.meetup.com/members?group_urlname=New-Zealand-Business-Intelligence-User-Group&key=XYZ

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;

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:

https://api.meetup.com/2/rsvps?event_id=230678189&key=XYZ

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;

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).

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

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

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

let
    event=EventID,
    Source = Json.Document(Web.Contents("https://api.meetup.com/2/rsvps?event_id="&Text.From(event)&"&key=XYZ")),
    results = Source[results],
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"venue", "created", "member_photo", "answers", "rsvp_id", "mtime", "response", "tallies", "guests", "member", "event", "group"}, {"Column1.venue", "Column1.created", "Column1.member_photo", "Column1.answers", "Column1.rsvp_id", "Column1.mtime", "Column1.response", "Column1.tallies", "Column1.guests", "Column1.member", "Column1.event", "Column1.group"}),
    #"Expanded Column1.member" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.member", {"member_id"}, {"Column1.member.member_id"})
in
    #"Expanded Column1.member"

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

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

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

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

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.

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

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

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:

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.