Easy way to retrieve Teams data in Power BI via Flow (and other data from the Graph)

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 😉

Export data from Power BI using Microsoft Flow

Edit 5th May 2019: Unfortunately this method will not work in the Power BI service!

In my last 2 posts I’ve described a way to automatically validate attachments from incoming E-mails. Microsoft Flow would watch for incoming E-mails, that match certain criteria and move their attachments to a dedicated folder. Then it would trigger a refresh of a Power BI dataset, that has been designed to check for errors in those attachments. Data driven alerts in Power BI would indicate if there are errors and trigger a Flow that sends an E-mail back to the sender, informing him that his attachments didn’t meet the agreed criteria.

In this article I will now explain how not just a trigger about the existence of a faulty attachment could be passed back to Flow, but also the corresponding data itself. Therefore I write a query that exports data from Power BI to Flow. But watch out: This is not suitable for very big tables. I experienced timeouts at tables with 300k rows already. Read more

Part 2: Automatically validate E-mail attachments with Flow and Power BI

In Part 1 of this little series I described the core-Flow on how to automatically validate E-mail attachments with Flow and Power BI. It automatically sends an e-mail to a business partner who sent an attachment, that didn’t meet the agreed specifications:

Automatically validate e-mail attachments – Part1

But before going live with this Flow, you should consider the following aspects:

Caveats

Read more