I've been working with PowerBI for a while and now I've set myself on a taswk to track Yammer data through PowerBI. I've managed to do a lot of things, but one seems to evade me:
How do I connect Topics with the Messages (Threads). I can't find a single common piece of information that would enable me to say: Ah, OK, so this topic is related to this thread. The URLs are different on the topic and the thread, the timestamp is often different (because we encourage hygiene in topicing), and so on. The only thing I do have is the creator ID, but that's not very useful.
Does anyone have an idea what the connection between the 2 tables could be?
It's not pretty, but this is how you can get it: The Topic ID (primary key) is embedded within the body of a post, and that post is assigned to a thread.
So Message ID is the primary key for the post, right? And if a post is part of a thread, replied_to_id denotes the Message ID that started the thread. Within the Body column is the text of the thread. When a topic is added, the string, "[Tag:<Topic ID>:<Topic Name>]" is inserted into the Body column. The number followed by Tag is the same number of the ID found in the Topics export.
You'll need to somehow programmatically extract those strings, and you can safely search for bracket followed by Tag and then a colon, to get where those Topic IDs are stored within the body string.
I would add another column to my data and populate it with code. Then use PowerBI to pivot off of that column and do a join with the Topics export. And like I said, you're joining the replied_to_id to the Message ID to get them all to match up.
Where the topic has been added to the thread using Add Topics, it doesn't appear in the body text of any of the messages. Can topics that have been added to threads be found anywhere in the export files?