If you follow me on Twitter (@ExcelAllison) you may have noticed I've been playing around with Power Automate and sending out automated Tweets throughout the Olympic games. I've really been enjoying following my two 'home teams' and watching the medal tallies tick up, with near-instant alerts.
Power Automate
Power Automate is a fantastic tool and really versatile in what it can do. It doesn't require any formal coding, though there is a bit of a learning curve at first and it does help to have some basic understanding of logical functions. However, if you put in a few hours a week to get up to speed with Power Automate, it could save you 2-3 days a month that you'll no longer have to spend on repetitive administrative tasks. I'm not exaggerating - there are studies and the time saved is real!
Objective
The goal of this exercise was to a) demonstrate the capability of Power Automate and b) drive traffic to my Tokyo Olympics 2020 Power BI report and blogs. I wanted to take advantage of the live data streaming throughout the Olympic games and engage with it on some level. I'm not very good at Twitter, but I knew that Power Automate has a Twitter connector so thought I'd give it a go - it's the perfect public forum for testing and showcasing the Power of Power Automate and Power BI together.
Born in the US but living in NZ, I wanted to keep up with both teams throughout the Olympics. NZ tv have been doing a good job of keeping us posted on the Kiwi results, but getting instant updates on the US athletes required a bit more effort.
Key Components
Okay, so let's lay out the concept from start to finish:
- Connect to live dataset using Power BI
- Create explicit measures for the key metrics we want to track (in this case total medals earned by NZ and total medals earned by US)
- Publish Power BI dataset
- Setup automated, scheduled refresh on Power BI dataset
- Create dashboard and manage alerts for the two key metrics
- Create flow in Power Automate that is triggered by the alert
We've already completed steps 1-5, so this post is all about step 6. As I started working through this process, I further developed step 6, thinking about what I wanted to accomplish with my flow and what my goals were. I wanted to:
- be alerted when my favorite teams won a new medal
- understand what they had won or what was happening in the Olympics
- share that joy with others
Power Automate has lots of connectors for alerts, notifications and information transfer, but Twitter seemed like the perfect platform for sharing the joy with others and also for figuring out what else was happening with the team.
I created two identical flows - one called 'Congrats Team NZ' and one called 'Congrats Team USA'. To simplify this post, we'll focus on the Congrats Team USA post (I used this one for testing purposes as USA got a medal before NZ and has earned more medals than NZ, so this flow has given me more opportunity to refine and test the process.)
Congrats Team USA
After a few iterations and learning a bit about Twitter and the Twitter connection, I have developed the 'Congrats Team USA' flow to complete the following tasks:
- Start whenever a new medal has been earned by US (as triggered by alert in Power BI dashboard)
- Search for Tweets from @TeamUSA on Twitter - limit search to 1 latest Tweet
- Retweet that Tweet (hope here is that it's related to the medal they've just earned)
- Post new Tweet - apparently this can't be the same Tweet over and over (and I'm sure my followers would get tired of it too) so I developed a list of inspirational quotes to add to this Tweet. In order to post the new Tweet, Power Automate will:
- Go to the SharePoint list of quotes, find the next in sequence and add that text to the Tweet
- Update the sequence of all quotes in the SharePoint list to move all items forward one place in line (getting ready for the next run of the flow with a new quote)
- Post the Tweet on Twitter
Twitter Rules
There are a few restrictions that Twitter and/or Power Automate place on your tweets that might be common causes for failure, so double check you've got all these in place if you want your flow to succeed:
- 280 characters or less: Tweets must be less than 280 characters! If you exceed this limit, your flow will fail.
- Avoid duplicates: You can't post the same tweet twice, nor retweet the same tweet, within a certain time period. Ensure your tweets are unique.
- Volume quota: Keep within the limits of the Twitter API
- # not @: Mentioning users or any reference to @ character will be removed from your tweet, but # and hyperlinks are ok.
How To
Alright, so how do we setup this Flow?
Step 1: Create SharePoint list of Quotes
From Teams or SharePoint, create a New List. Lists are a super cool app that probably deserve an entire post to themselves, but I'll go through the essentials here. I created a new Blank List.
By default, this list will have a single column called 'Title' that must be single line of text and is a required field.
Click on the Settings cog at the top of the list and choose 'List Settings'
Scroll down to find the 'Title' column and click on the link to edit the column. Untick the required box and Save.
Add a new column - multiple lines of text and call it 'Tweet Text'.
Add a new column - number and call it 'Sequence'.
We'll use both of these new columns in our Flow.
Now click on 'Edit in grid view' and add some inspirational quotes or Tweet messages. Remember the character limit! For the 'Sequence' column, start with 1 and increment by one each row.
Click 'Exit grid view' to save your changes.
Step 2: Create Flow
Navigate to Power Automate and click 'Create' to start a new flow.
Click 'Automated cloud flow'
Type 'Congrats Team USA' (or fill in your team name).
Trigger: When a data driven alert is triggered
Search for Power BI in the triggers. Select 'When a data driven alert is triggered'. Click 'Create'.
Search Tweets
Click 'New step' and search for Twitter. Add the 'Search tweets' action. At this stage you will be asked to login to your Twitter account to create the connection.
Enter your desired Search text. In my flow I put 'from:@TeamUSA' as my search text.
Expand 'Show advanced options' and limit the maximum results to 1.
Retweet
Click the 'New step'. Search for and add 'Retweet'.
From Dynamic Content, add the Tweet Id from the Search Tweets step. This step will automatically get added to an 'Apply to each'. Even though we limited our search results to 1 Tweet, Power Automate still sees it as a list of Tweets, so will Apply the RETWEET to each search result.
Click the three dots at the top right of the Retweet step and ensure it's using your signed in Twitter connection.
Variables: Quote and NewSequence
Variables are basically a box to hold information. You can then move and reuse this information, or even add and subtract from it. Variables MUST be initialized in Power Automate before you can use them. In this Flow, I created a String variable for 'Quote' and a Float variable for 'NewSequence'.
I left value blank for both, we'll assign this later.
Get Items (SharePoint List)
Add a new step for 'Get Items'. This will return the values of the columns in a SharePoint list.
Select your Site Address and List Name that we created in Step 1 from the drop down.
Apply to each SharePoint List Item
Add a new 'Condition' to the Flow. Choose 'Sequence' column from the Get Items step. This will automatically wrap the Condition inside an Apply to each step. Everything we do in here will now be done to EACH item in the SharePoint list.
We want to find the next Quote in Sequence - our list starts with Sequence = 1, so that's the quote we want. Ensure your Condition looks like the image above: Sequence is equal to 1.
If yes, add a step for 'Set variable'. Set Quote text Variable to 'Tweet Text' column from the Get Items step. Since we're still inside the Apply to each, this will happen for each item, IF the Sequence is equal to 1. If no, we'll leave that blank.
Now add an action for 'Set variable' below the condition, but still inside the Apply to each. We want to shift ALL the items in the list (not just the one we chose for the quote) so that they move 1 place in line and the next list item ends up with Sequence 1 for the next time this flow runs.
- Set the 'NewSequence' variable equal to value of 'Sequence' column from the Get Items step.
- Add a new step for Increment Variable. Choose the 'NewSequence' variable and type -1 in the value to increment.
- Add an new step for Update Item (SharePoint list). Select your Site Address and List Name that we created in Step 1. Put the ID from Get Items into the ID field. Put NewSequence variable in the Sequence field. This moves every item one step forward in sequence/line.
Post Tweet
Below the Apply to Each, add a new step at the very bottom of the flow and search for: Post a Tweet.
Type your Tweet text, be sure to include the Quote Text variable in there somewhere and follow the Twitter character limits!
Save & Test
Once you're happy with your Tweet, save it and test when the Power BI alert triggers. You may need to come back to edit and troubleshoot some small errors. Remember - all testing in Power Automate is live to the data connection. You may want to use a test Twitter account or SharePoint list for testing. When you're ready, you can easily update the connections to the live data sources and Twitter accounts.
Enjoy!
