#6 Lesson — Part 2 | Learn to Code with Microsoft M-Language: Real World Project — Pull & Clean up Data from Meetup.com via API

6 min readMar 29, 2018

I assume you have signed up at Meetup.com by now. If not, please do so (it doesn’t cost a dime). Once you have an account, please log in. This is how your home page should look like, more or less.

Please scroll down to the bottom of the page and click on API.

You should see this.

Scroll down a bit and click on the link underneath v2 categories.

You will be directed to a documentation page which explains what this specific API does that we just selected:

“Returns a list of Meetup group categories.”

This is an important piece of information, as you can later browse Meetup.com APIs by those categories you are concretely interested in. Next, click on Try in the console.

You will be presented this view.

Scroll down, clear the page field and then click on Show Response (important note: If you don’t clear the page field, you will only gather 20 random Meetup.com topic categories! Instead, we want all of them).

What matters to you right now is the Signed URL. You can click on What is this? if you want to understand what a Signed URL is.

“Similar to OAuth signing, this method of authentication certifies that a request was approved by a particular user. Unlike OAuth-signed requests, key-signed requests may be reused and recycled as long as their corresponding API key is valid. If a signed URL is released to the public, any application can use it to interact with Meetup as if it had that API key; the difference is that it can not change definitive parameters or use the signature against other API methods.” Meetup.com

In essence, Meetup.com provides an identifier that is unique to your account & API key. Make sure you don’t share your unique URL with others, I have anonymized parts of my URL below.

Grab the Signed URL and open a new Excel Workbook. On the Data Ribbon, select the From Web icon and paste your URL into the corresponding field, then hit OK.

A new query will open, and you will be presented this view. Click on List.

You will see a list of records. What do we need to do next? What do you think? Correct! We have to convert that list of records into a table. Hit the To Table button.

Select the default setting when asked how to handle delimiters and extra columns (Remember? We did the same in the previous lesson already!).

Presto, here’s our table. We now want to look inside those records. Click on the double-arrowed Expand icon.

Next, uncheck Use original column name as prefix and hit OK.

Look at that! We now have the category IDs for the zillions of venues on Meeup.com.

Let’s sort the result in an ascending order. Just select the category_ids column, hit the down arrow and click Sort Ascending.

Nice! We now have a neatly sorted list of topic IDs from arts to writing.

Let’s clean up that sheet a bit. First, let’s remove the second sort_name column, as its content is redundant with the first column’s one. Right click the column name and select Remove.

Just a few more minor cosmetic changes. Let’s capitalize the first letters of the column names, and let’s change the cryptic Query name under Query Settings > Properties > Name to MeetupCategories.

Let’s take a final look into the code. Click on Advanced Editor.

I will not walk through the code in greater detail as I did in the previous lesson. But as you can see in the first line of the Let expression: We got the API URL as Source for our Query. In the next part of this lesson, we will take a closer look at Source and modify our code for automated querying. It’s gonna be fun! (becuase it will spare you lots of mundane, repetitive work)

One last thing, let’s load the results of our query into an Excel Sheet and save our Excel Workbook. On the Home Ribbon hit the upper part of the Close & Load icon.

Congrats! We have a presentable table in an Excel Sheet now.

Now save the file as MeetupCategoriesIDs. We’re done for now.

Before we jump into the next part of this lesson though, let’s recap briefly what we just did:

We generated a unique API call URL on Meetup.com
We specified what data we need in Meetup.com API Console
We then pulled that data into Excel
With help of M-Language, we transformed the data and thus made it digestible
Finally we loaded the data into an Excel Sheet

That’s a lot! Let’s move on and use the data we just gathered to look into one specific category: What venues does Meetup.com host in different locations?

#6 Lesson — Part 2 | Learn to Code with Microsoft M-Language: Real World Project — Pull & Clean up Data from Meetup.com via API

This is my entire course

Written by SPRDsheet