Stay hydrated 💧💧💧 using Microsoft Teams, Flow and PowerBI
Recently, I was looking for some app to remind me to drink water and monitor my waterintake. I know you might say - JUST REMEMBER IT YOURSELF! However, I am a geek who likes to use technology to give me some nudges. So, I reviewed a bunch of apps but none of them had the exact thing I was looking for. Some of them were too fancy and some were asking to PAY for the “pro” version. I was definitely not going to spend money to just give me reminders , so I ventured to build something from the Microsoft suite of tools which was more integrated with my work life and was the best way to actually make me drink more.
The big idea
The main requirement was an effective way to “nudge” me to drink water and at the same time to be able to monitor how much I am drinking on a daily basis.
I used Microsoft Flow to send me hourly reminders in Teams as an adaptive card, where I could select how much I drank. This was then recorded in a SharePoint List which was then used as a database for a PowerBI dashboard. Sheesh, that sounds complicated but believe me it’s much more easier than it sounds. So, let’s get started.
Step 1: Create a SharePoint List
SharePoint list is probably the easiest data source for this, however, you can also use Excel in your OneDrive. The list has two columns “Amount of Water” to record the quantity and “Time” for recording when I drank.
Step 2: Let’s Flow
Quoting Luise Freese - “Flow is the glue that holds everything together”. That’s exactly what we are using Flow here for - to connect to SharePoint , Teams and PowerBI.
Here is a snapshot of the Flow -
You can download the Flow .zip file from github here - https://github.com/thatapiguy/Monitor-water-intake-using-Flow-Teams-and-PowerBI
Now, let’s break down the flow a bit.
1. My intention was to run the flow everyday from 8 AM to 4PM which is when I am most likely to “obediently” follow the reminders. 😀 And for that I used the recurrence trigger.
2. Next, I used the “Current time” and “Convert time zone” actions which I will be using later to check if the time is between 8 AM and 4 PM. I also initialized a variable to store the amount of water I drink.
2. Next, I am checking if the current time is between 8 AM and 4 PM. To do that, I used an expression to first get the hours value and then converted it to integer. And then I compared if it’s greater than 8 and less than 16. To get more info on standard date and time format strings, go to this docs link and bookmark it NOW! Trust me.
int(formatDateTime(body('Convert_time_zone'),'HH'))
3. Now comes the most important part of the Flow - the reminder to drink. To send the reminder , I am using the Teams action - “Post a choice of options as the Flow bot to a user”. It’s kind of similar to an approval e-mail with options. It will basically send an IM to myself as an adaptive card with multiple options to select from. And then the Flow will wait for me to select and submit an option. Just for fun, I made the options as water emojis 💧💧💧 (did you know you could use windows key + ; to insert emojis on your windows computer?)
4. Next, I set the variable AmountOfWater to either 100, 200 or 300 based on the emoji selected using the Switch-Case action. After that I used that variable as the Title of the new record in the SharePoint list and also recorded the time.(using the Create Item action). That’s it, the Flow is done.
Step 3: Setup a PowerBI dashboard
Click on “Get Data” and then select the “Create new content” with Files. Then select SharePoint - Team Sites and enter the SharePoint site where your list is located.
Once connected, you can create a simple bar chart with Time as your X-axis and Title/Amount of Water as your Value. One thing that I had to change was the data type for both the fields using PowerBI desktop as it didn’t recognize it correctly. (or probably I was doing something wrong as I am a noob in PowerBI 😄)
Step 4: Obediently follow the reminders in Teams 😀
The good thing about Teams adaptive cards is that it works seamlessly on your phone as well. So even if you are not on your computer , you can get reminders on the phone.
Andd… that’s a wrap! I know this is no business use case , however this integration between Teams adaptive cards, Flow, SharePoint and PowerBI can be used for so much more. This was just a fun way to understand the integration. And hey, in the process if you can stay hydrated as well, that’s a bonus.
Please tell me that you had at-least one sip of water while reading all this. If not, do it now. I am getting the reminder as well. 💧
Don’t forget to follow the blog to get the latest content on Power Platform.
Also, you can follow me on Twitter and subscribe to my channel on YouTube .
Comments:
Nick -
Very nice! I’m still lost with the logic behind Flows but I like this tutorial, will try it soon :)
#### [Phill Harris]( "[email protected]") -
Although not business-related, I love this as a proof-of-concept. Great job—can’t wait to create it here!
#### [Phill Harris]( "[email protected]") -
This works great…thank you. How would I update the flow to terminate if there’s no response say, by the end of the current day? The flows I created keep running if there’s no response until they exceed 30 days. Thanks
#### [Stay hydrated - Learn about Variables and Functions in Power Automate - Gezeitenbrand](https://gezeitenbrand.de/stay-hydrated-learn-about-variables-and-functions-in-power-automate/ "") -
[…] into his tutorial and tried to rebuild that. With every flow you build you learn […]