Ever found it hard to keep up with all of the Meetup.com events you are interested in? Same here. So as a Power BI person I thought I'd try & collate all of the Meetup's I know and produce a much easier view of what's events are coming & have some level of interaction with the report.
I have shared all of the code in a pbix file, which you can get down the bottom of this page.
Here's the finished product:
Getting access to the data was not the easiest. Each Meetup has a calendar you can connect to, with four format options of RSS, Atom, iCal and Outlook. The Outlook one is great to sync to your own Outlook or Office365 calendar, but is cumbersome if you want to sync more than 3-4.
You can access your Meetup.com calendar at this link:
At the bottom you'll see the option to Subscribe to a feed of this calendar. As an example, check out this link to the Dublin Power BI User Group, which I help run: https://www.meetup.com/DublinPUG/events/calendar/
I tried the RSS and Atom options in Power BI, but the date & time of the event was buried in the description in HTML tags. The Outlook option is a webcal:// link which isn't supported in Power BI's Web data source (Web.Contents). I thought I'd attack this using the iCal option. Don't get me wrong, the RSS and Atom options were probably better choices, I just wanted to try out a different option.
The iCal is essentially a text file formatted for upload into the Outlook Desktop App. The structure isn't purely rows/columns, but a plain file with events listed one after the other & the data structure with tags. The below screenshot shows an example. Notice the header of the file starts with BEGIN:VCALENDAR, and each event starts with BEGIN:VEVENT
To get this into the format I wanted, I used a couple of handy Power Query functions. This is the process at high level:
Load the file using Table.FromValue(Text.FromBinary(Web.Contents("link"))) which gives me the file in a plain text. The link to the iCal is a direct file download, not a web page as such, so I needed to read the file contents (Text.FromBinary) and turn into a table (Table.FromValue)
Use the BEGIN:VEVENT text as a delimiter and turn the vertical events into columns
Unpivot the columns so each event becomes a row, the content still all contained in a single cell.
Using Text.BetweenDelimiters() I was able to parse out all of the data into separate columns. This was done one at a time as I needed a delimiter start & delimiter end point for each piece of data I wanted to extract. In some cases I needed to use a carriage return as the last delimiter, so the Power Query code would look like this:
Once I had the query sorted for one user group, I needed it to be repeatable for a list of user groups.
Firstly, I used a parameter and set the eventurlcode to use this parameter, by building up the URL as follows:
Notice in the Web.Contents function I'm using the base URL as the first parameter and the rest of the URL using the RelativePath option as the second parameter? I needed to do this to allow the Power BI Service to allow me to schedule the refresh once I had published it. This is because Power BI needs to validate the URL, but if it's generated via variables at runtime, it will fail this process.
Secondly, I turned the query into a function so I could call it for multiple Meetup events without having to re-write it.
Lastly I created a list of events I wanted to add into my report. Getting the list was quite manual as I needed to search meetups I was interested in, get the eventurlcode and put into a "master" list.
The Master list could have been in a spreadsheet or text file, or some other typical meta-data type source. However given the sheer simplicity I created a Power Query to generate that list using Table.FromRows(). If I want to add more, I'll simply edit the query. The main reason is I wanted the .pbix file to be fully independent and not rely on some other file source & created another artefact to maintain & ensure queries were correctly pointing to it.
Once that was done, I wrapped it all up nicely and started building visuals.
I used three custom visuals: Advance Card which I personally favour for the the very basic metric/box visuals and for showing "Last Updated". I used Calendar Visual by MAQ Software for the diary view, I tried several others but this looks the best for this scenario. I used the Text Wrapper by MAQ Software for the Tooltip (when you mouse over the Coming Soon table, don't miss that little bonus!)
I've used my standard Page Background which consists of a blue & orange banner and company logo. It's very basic & I incorporate this simple design at most customers. It's nice for branding.
You can review this report and a few others in our Datalineo Power BI Gallery and you can get a copy of the .pbix file from my GitHub link.