In a previous post I’ve described how to use a custom connector to retrieve data from the Microsoft Graph API. But this requires to register an App and adjusting the M-code in the connector itself requires some M-knowledge. So I thought it might be a good idea to share an alternative method to retrieve data from Teams for example, that works out-of-the-box. There are a couple of endpoints supported currently:
Also, these triggers are available:
I’m using the “Get messages” to retrieve all messages from a teams channel.
High level overview
Fetch the data with a standard-connector from Flow and save it to a JSON-file on your drive. From there you import it via a standard JSON-connector into Power BI.
In Flow
I started my flow with schedule trigger that refreshes it daily at midnight. Then get messages from Teams by selecting the Team- and Channel-name. You will have access to all the teams that you’re part of.
Please regard that this connector is in preview at the time of writing, so could still change. What I hope is, that the connector will be extended to include filters (for dates for example) as well, so one doesn’t have to pull all conversations with each call.
That step returns a JSON that I want so save into a file as it is, as it’s so much easier to extract the data from a nested JSON like this in Power Query than in Flow. Therefore you can add a compose step or transfer the returned body directly into the Create file-step with a little trick.
1 Compose
2 Directly
In the field “File content” you choose the “Expressions”-tab from the dynamics expressions. There you just type a space –> click on tab Dynamic Content –> choose “Body”. That will create the following formula that you simply accept: body(‘Get_messages’) (of course, you could also quickly type that in manually if you want to avoid this slightly fiddly clicking.
Back in Power BI, I import it as a JSON and simply expand all the columns I need and filter as desired.
Enjoy & stay queryious 😉
