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.
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.
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 😉
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
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:
Receiving files from business partners that don’t match the agreed requirements/formats causes all sorts of problems in daily business. I found it particularly disturbing during month-end closing when time is really tight: You have a strict rule in which order each process has to run and there are many dependencies between them. So when then one import doesn’t work, many other processes will come to a halt as well. Fortunately, today there is a simple remedy for it: Automatically validate E-mail attachments with Flow and Power BI
Process Automation with Flow and Power BI
You can create a Flow that “listens” for incoming emails in a mailbox that match certain criteria and contain attachments. Flow can then extract these attachments and save them to an online-folder. After that, Flow triggers a refresh of a Power BI dataset, that imports these attachments and checks for the data quality-criteria that you have defined. Then you create measures for the data quality that trigger data driven alerts from Power BI service. Flow then listens for these alerts and sends an email back to the sender, requesting for a corrected file.
This not just saves crucial time, but also your nerves (and those of your team-mates).