This is the second post in the series “Yammer Analytics in Power BI”

1 – Intro to Yammer Analytics in Power BI

2 – Paging through Yammer group messages

3 – Paging through Yammer user messages

In the first post I shed some light on simple Yammer authorization approach and general Yammer API URLs.

In this post, let’s try to build a function to get group messages with their attributes.

As I mentioned in earlier post, Yammer API query to group messages has following URL pattern

https://www.yammer.com/api/v1/users/in_group/[groupid].json

It returns only 50 messages per query, which include both – thread starting messages and comments. We will need a loop to get all messages we need.

And we are interested in the messages that are older than a certain date.

Current API is very limited and only allows to use following parameters

No “date”-field, unfortunately.

As you may see, current API is not an Odata-kind of API.

We only have a “newer_than” parameter, which can help to restrict # of messages.

It accepts a number – “message ID”. Therefore, we need to query messages page by page until we get a message with “creation_date” older than required.

Query Yammer group messages

Firstly, I decided to get an ID of the last message in the group to have a starting point for further queries.
Even before that, I had to create two parameters that will be re-used in queries:

And another parameter to limit messages by date

Query to get the latest message ID:

Pretty simple function: query, buffer, unpack json.

Yammer API for group messages returns sorted list of messages, sorted by ID descending. So, Top 1 message is the latest published.

It helped in the main query with paging: last message in the list is always the oldest.

After playing around a bit, I ended up with following two functions:

First: query pre-defined URL and return json (or null)

Second: main iterator going through pages (using List.Generate) – extended with fields for debugging

What was tricky is to include the last page with messages, where we find message that meets condition “LastMessageDate < Date.From( DateFrom )”. That page can contain messages with creation_date older than DateFrom and newer. Therefore, it shouldn’t be ignored.

However, List.Generate behaves in a way: when condition is met, step is ignored.

Can’t say what would be the ideal solution here, but I ended up with calculating a “variable” that referencing to previous step

found_last = ( [LastMessageDate] < Date.From( DateFrom ) )
or [more_available] = false, // reference to previous step of list.generate

Then I check this “variable” in condition

Result

Information about Likes (Liked users) and Attachments comes as records from API. Those records can be expanded into list, what we usually do not want to do. So I had to load messages into one table, Likes and Attachments – into two separate tables.

“Liked_by” comes as a record which can be expanded into list of UserId values

“Attachment” is a bit more complex object than “Like”, but they have something in common. Message may have several of both:

Depending on attachment type list of record fields is different. For a “page” is probably the simplest

For image or video attachment records have more fields.

That’s all for today.

Next time I’ll describe my approach for getting group members, which is also based on List.Generate but slightly different from used for messages.

Leave a Reply