Tokyo 2020: Power Query Data Source Update and Error Troubleshooting

Lots of swimming, cycling and more happening at the Tokyo 2020 Paralympic Games. Team New Zealand have already won a Gold and Silver in swimming. Still plenty of time to earn some more. USA have earned Athletics, Cycling, Equestrian and Swimming.

I spent a bit of time creating a report for the Tokyo 2020 Olympics, and the Paralympics follows a very similar format with the same type of information and data to report. Therefore, it should be straightforward to update the report from Olympics to Paralympics, right? 

In theory. In practice, this is sometimes more challenging than it should be and we encounter all sorts of errors along the way. 

Well, I succeeded. Here's the Power BI Report with live data for the Paralympics, updated every 30 minutes:

Change Data Source

In order to update my existing report, I need to simply point the report to the Paralympic-Games link instead of the Olympic-Games link. 

Old Data Source: 

https://olympics.com/tokyo-2020/olympic-games/en/results/all-sports/medal-standings.htm

New Data Source:

https://olympics.com/tokyo-2020/paralympic-games/en/results/all-sports/medal-standings.htm

Unfortunately, not all the column headers are identical, so this is fraught with a few minor challenges along the way. 

In the video below, I demonstrate how to change and update your data source quickly and without stress, even when you encounter errors in Power Query.

Preview Feature: Data Point Rectangle Select

Today is the day New Zealanders will learn the fate of COVID 19 Lockdown levels for the next few days/weeks. Those of us in Auckland are guaranteed Level 4 through Tuesday, but the rest of the country holds hope of moving down to a lower level in time for the weekend.

What do the numbers look like? 

Since we've had a long (enjoyable) gap with no Community COVID 19 cases in NZ, I thought this would be a good excuse to play around with the Power BI preview feature: Data Point Rectangle Select

Data Point Rectangle Select

In order to use this in your reports, you must:

Step 1: Enable the Preview Feature

Open Power BI Desktop

Click File > Options and Settings > Options

Tick the box to enable 'Data Point Rectangle Select' and click OK.

Restart Power BI Desktop.

Step 2: Use It!

If in Power BI Desktop, you'll need to use the Ctrl key to activate this feature. If in Power BI service it works with just a simple drag and drop. 

That's it! You've now got the power to select specific areas, outliers and ranges using visuals as filters. 

Demo

Below is a short 1 minute video I made on Wednesday, showcasing how I used this feature to hone in on the most recent COVID 19 outbreak in New Zealand and compare that to our first outbreak and the only other time we were in Level 4 Lockdown. The numbers look pretty comparable, but hopefully more isolated to one part of the country.

Stay Safe NZ!

BONUS: Toggle and Bookmarks

At the very end of the video, you'll notice I toggle the map to show the COVID 19 cases for all time across New Zealand, rather than focusing just on the current active cases. I have a tendency to go overboard with Power BI DAX, visuals, and features, which can lead to information overload if you're not careful. 

Historically I've struggled with deciding what information to cut, or how to make it easy for the user to choose without taking up precious real-estate on my screen. 

Well, the Power BI toggle might just be the perfect solution. Thanks to Havens Consulting: Creating Sliding Toggles With Native Buttons in Power BI for the tutorial and David Johnston's winning Enterprise DNA report for the inspiration to toggle! Check out their videos and let me know your thoughts on toggles.

Refreshing COVID-19 NZ Data: RelativePath and Query options with the Web.Contents M function

The first of the Paralympic Games medals have been earned already - I'll be posting a blog soon on how to use .pbit files and update data sources using parameters. 

COVID 19: New Zealand is now in Level 4

Today though I'm going to digress slightly from the celebratory mood of the Olympics and return to the COVID 19 data analysis. I've had a few requests from friends and family to dig up my old report and visualize what's going on in the NZ community (not including MIQ facilities). View the full COVID 19 NZ report in Power BI.

Question: What do the NZ Community COVID cases look?

The Ministry of Health website graphs all NZ cases, including those in Managed Isolation and Quarantine. It's difficult to get a good picture of how many community outbreak cases we're dealing with, and how that compares to the other outbreaks we've seen in New Zealand. 

So what does the COVID 19 data look like over time if you remove the Managed Isolation & Quarantine cases?

Unfortunately, the Ministry of Health website has changed quite a bit since I first created the COVID report, and there are no longer any web based tables available to use as data source.

Challenge: Auto Refresh a Dynamic Data Source

Instead, all the data I need is uploaded daily in a CSV file which includes the name of the date in the filename. The web link to access this CSV file is: 

https://www.health.govt.nz/system/files/documents/pages/covid_cases_2021-08-25.csv

In order to make the refresh dynamic, I'm going to use a few Power Query tricks. 

Step 1: Identify Data Source parts

The CSV file seems to be uploaded between 1-4pm each day for that day, so if I schedule the refresh for 4pm daily I can use today's date as part of the data source URL.

Recall the URL is: 

https://www.health.govt.nz/system/files/documents/pages/covid_cases_2021-08-25.csv

We're going to break this into:

• Root website: https://www.health.govt.nz
• Relative path part 1: /system/files/documents/pages/covid_cases_
• Relative path part 2: 2021-08-25
• Relative path part 3: .csv

Note the Relative path part 2 matches today's date in the format: yyyy-MM-dd

Step 2: Generate Dynamic Relative path part

I can't use a normal parameter for the Relative path part 2 because I need it to update automatically on refresh. So, I'm going to create a new blank query instead. This Query is really simple, just has one step that combines a few M code date functions: 

let

    Source = Date.ToText(Date.From(DateTime.FixedLocalNow()), "yyyy-MM-dd")

in

    Source

I have named this query: DateCSV

Step 3: Use relative path to get Dynamic Data

Normally we'd be able to split the URL into parts, but with a dynamic data source that will give an error.

INCORRECT Code

let

    Source = Web.Contents("https://www.health.govt.nz/system/files/documents/pages/covid_cases_"&DateCSV&".csv")

in

    Source

Unfortunately, if you use the above Source code and Publish to PowerBI.com, you'll get the following error explaining that dynamic data sources cannot be refreshed in the Power BI service: 

This error is not very helpful - it doesn't even specify the correct name of the query, instead just referring to 'Query1'.

When you follow the link to Learn more from the error message, you will find a short message that doesn't quite provide you with enough information to resolve the data error: 

In most cases, Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

Picking up on the keywords, a quick web search for "RelativePath and Query options with the Web.Contents M function" returns Chris Webb's super helpful blog.CrossJoin post Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code, to which I have referred many times.

Finally, we're getting somewhere and you may finally be able to see why I split the URL into a Root path and four Relative paths in Step 1.

CORRECT Code

let

    Source = Web.Contents("https://www.health.govt.nz", [RelativePath = "/system/files/documents/pages/covid_cases_"&DateCSV&".csv"])

in

    Source

This correct code uses a static URL for the Web.Contents() function. You'll find this process easier if the root URL is an actual functioning website, which is why my Relative Path part 1 is still static.

Now we can refer to the dynamic DateCSV query within the RelativePath portion of the Web.Contents() function and overcome the dynamic data refresh limitations in Power BI service.

Voila! Now we can schedule a refresh on this dataset in Power BI service.

Categorical Date Slicer in Power BI

Today's post is inspired by @Iguima of the Power BI Community. You can read their original Date Categorical Slicer question on the Power BI Community Desktop Forum.

The original poster would like to achieve a categorical date slicer that enables the report user to filter the Sales (fact) table for all data AFTER the selected event/date category. Something similar to the final result below:

Note the events span over 20 years and include the 911 terrorist attacks on the USA, COVID 19 pandemic and also some not so catastrophic events such as iPhone first release date. I have added a few more recent events for 2020-21 in case you want to test on your own sales data (which might not span 20+ years!).

Event Name	Event Date
COVID 19 Outbreak	Wednesday, March 11, 2020
Joe Biden wins US Presidency	Saturday, November 7, 2020
Taliban take over Kabul	Sunday, August 15, 2021
911 Attacks	Tuesday, September 11, 2001
iPhone Released	Friday, June 29, 2007

For reference, I have highlighted the important parts of the original question: 

There are two key issues in this request: 

1. We want to filter for data only AFTER the selected event. Most filter behavior will return data only ON the selected event.
2. We want this to be responsive to the filter and slicer selections made by the report consumer.

I have already written about key issue number 2 in a previous blog on Reporting Order of Operations. Basically, calculated columns do not respond to report filters and slicers. We need to solve this problem with a MEASURE.

Data Model

Before we can get into the measure and DAX, we need to review the Data Model. Any of my former students will tell you this is the foundation of your Power BI report. Without the correct data model, DAX won't work. 

For this scenario, we want the Important Event to act as a bookend for our Date table, not filter it just for that specific date. Therefore we need to ensure that the Important Events table is NOT related in any way to the other tables in our data model. 

Below I have a very simple data model: Calendar (date dimension) and Sales (fact) with my Important Events (Categorical slicer table) unrelated to any of these.

DAX Formula

Now that we have the Data Model sorted, we can move on to the DAX.

We start with a simple measure for Total Sales:

Total Sales =
SUM ( Sales[Sales Amount] )

Use this [Total Sales] measure in a visualization by Calendar[Year].

We then add a slicer for the 'Important Events'[Event Name]. Since the Important Events table has no relationship to our data, this won't do anything in our report. 

In order to get the Total Sales to filter based on the slicer, we need to figure out what value the user has selected. I have chosen to use the MIN function in case multiple events are selected. This will ensure we are looking at all data after the earliest event.

Selected Event Date =
MIN ( 'Important Events'[Event Date] )

NOTE: Because the [Selected Event Date] is a measure, it will respond to user selections in the slicer as per the Reporting Order of Operations blog. 

Finally, we can use this measure as a filter. You may initially be tempted to write the following DAX and get an error: 

I haven't used CALCULATE within the filter, but I have used [Selected Event Date] measure, and every measure has an implicit CALCULATE function in front of it. Therefore this DAX expression is not allowed. 

Instead, we need to provide a ROW CONTEXT for the calculation, here I use the FILTER function to provide this context. Now, I am able to use the [Selected Event Date] measure (with its hidden CALCULATE function and all) inside the filter expression:

Sales After Selected Event =
CALCULATE (
    [Total Sales],
    FILTER (
        'Calendar',
        'Calendar'[Date] > [Selected Event Date]
    )
)

Hurray! We get the final result we're looking for, as per the GIF at the start of this article. 

CONCLUSION

You cannot use COLUMNS to respond to filter/slicer selections.

You must not have relationships if you want to do before/after/range filters. 

Once we have overcome these two obstacles, the desired result is achievable. Now to just update any other measures with this added filter. 

Tokyo 2020: Per Capita Medal Rankings

Finally, the results you've all been waiting for!

I've had students, fellow MVPs, Aussies and New Zealanders alike ask for the per capita medal rankings for the Olympics. Ask and you shall receive. 

For this data, I've merged the population data from Wikipedia into the OlympicTeams table and calculated the Medals per 1 Million people for each country. 

Happy to say that it's an extraordinary year for New Zealand - we're ranked number 2 per capita in Gold First medal rankings. I've also replaced the United States with Australia in this per capita page as we have to keep the trans-Tasman rivalry alive and well. Australia you have some catching up to do - NZ have more than double the per capita medals with 2.93 to Australia's 1.39 as of now. 

Keep up to date with the live results in the report below (per capita is page 2).

Enjoy the Olympics!

Tokyo 2020: RANKX

I'm sitting here watching the Olympic action in the Velodrome for men's pursuits finals and proud to say that New Zealand is currently in the top 10 teams overall in the Olympics! Well, as long as you're using the 'Gold First' ranking method that is. They're number 12 if you are ranking by total medals count. 

Olympic Medal Ranking System

Lucky for New Zealand, most of the world goes off the Gold First ranking method. This is certainly how they have been ranking the teams on New Zealand TVNZ 1 throughout the Olympic games. 

However, that is not always the case in the United States. I recall watching broadcasts of Olympic games where the total medal count is all that determines the order of the teams. Looking at the stats, it's not surprising that the United States does things differently (were you surprised anyway? We often march to the beat of our own drummer in the USA). 

Tokyo 2020 Ranking Controversy

Do a quick Google search and you'll see some controversy around the USA media Olympic team ranking systems. Here's an excerpt from Independent.co.uk:  

Historically, the difference between the Total Medal Count and Gold First Ranking has only mattered in a few years, such as 2008 when China had most Gold but USA had most Total medals.

The USA and China have been battling for first place throughout the Tokyo 2020 Olympics, with ROC and Japan high on the list as well. Currently, USA ranks first in Total Medal Count, but only second (to China) in Gold first ranking. Come on team USA - bring home the Gold!

RANKX DAX Function

I thought I'd take this opportunity to write a post on the RANKX function in DAX. This is a complex function that I have spent many hours researching, testing and tweaking. What makes the RANKX so difficult to get right?

• Dynamic measures - when using RANKX in a measure, you need to be aware of the DAX context and ensure you use the needed modifiers, such as ALL and CALCULATE, otherwise you'll end up with every team ranked number 1!
• Ties - how do you break ties in RANKX? This often becomes a math problem, and one that I need to solve for the Gold First ranking method

The DAX Measure

Gold First Rank =
IF (
    CALCULATE (
        [Tokyo 2020 Total Medals],
        ALL ( OlympicMedals )
    ) > 0,
    CALCULATE (
        RANKX (
            ALL ( OlympicTeams ),
            CALCULATE (
                [Tokyo Gold] * 10000 + [Tokyo Silver] * 100 + [Tokyo Bronze]
            ),
            ,
            DESC,
            SKIP
        ),
        ALL ( OlympicMedals )
    )
)

Gold First Ranking System

Let's start by understanding the Gold First ranking system in the Olympics. It's pretty straightforward - the team with the most Gold medals is ranked 1, next most Gold medals is number 2, and so on. But what if two teams have the same number of Gold medals? Then we look at Silver for the tie break. 

For example in today's rankings, France, Republic of Korea and New Zealand all have 6 Gold medals, but France have more than twice as many Silver medals with 10 Silver and are therefore ranked in 8th, higher than Republic of Korea and New Zealand. Republic of Korea and New Zealand each have 4 Silver medals, so are again tied. Therefore we look to Bronze for the tie breaker - Republic of Korea have 9 Bronze to New Zealand's 5 Bronze, therefore Republic of Korea take 9th and New Zealand take 10th place. Just 1 Gold medal and New Zealand could overtake both France and Republic of Korea to gain 8th place!

Rank Expression

In order to rank these teams accurately, I need to calculate a value or 'expression' that can be used to rank them. We need to ensure that Gold is given the most weight, Silver next and Bronze the least.

In order to make sure that a country with lots of Bronze and no Golds is not ranked above a country with 1 Gold, we need to choose the appropriate weighting. To determine this appropriate weighting, I will start with the smallest value. 1 Bronze medal gets 1 point. 

Next, I need to understand my data and ask a very important question:

What is the maximum number of Bronze medals a single Team might win in a single Olympic games?

The USA have won 701 Bronze medals in total (the most of any team), so it's definitely less than that. In Rio, 359 Bronze medals were awarded to all the Teams, so again, a single team will not earn more than that. I have decided that it's highly unlikely that a single team will earn 100 Bronze medals or more in a single Olympic games. Therefore I'm granting 100 points for a Silver medal. This ensures that a Team with 1 Silver medal will always beat a team with 0 Silver medals, even if that 0 Silver Team have earned 99 Bronze medals. Okay, now how many points should a Gold medal be worth? I have chosen to give Gold medals a weighting of 10,000 points. This means that again a Team with 0 Gold medals and 99 Silver and 99 Bronze will have 99*100+99=9,999 points, but still not enough to beat a Team with 1 Gold which earns them 10,000 points.

Okay, now that we've got the mathematics out of the way, let's look at the RANKX function. 

RANKX (
            ALL ( OlympicTeams ),
            CALCULATE (
                [Tokyo Gold] * 10000 + [Tokyo Silver] * 100 + [Tokyo Bronze]
            ),
            ,
            DESC,
            SKIP
        )

Starting from the inside, we see our weighted medal expression: 

 CALCULATE (
                [Tokyo Gold] * 10000 + [Tokyo Silver] * 100 + [Tokyo Bronze]
            )

which assigns the points we have allocated to each Gold, Silver or Bronze medal. I have put this inside a CALCULATE function for completeness and out of habit, but since we aren't using any aggregate functions it's not necessary, I just find it helpful when working with row context to always use the CALCULATE.

This expression will be evaluated over the row context of the entire list of OlympicTeams. We MUST use the ALL function here in row 2, or else every Team will be ranked as 1.

   ALL ( OlympicTeams ),

Using ALL OlympicTeams ensures that we compare the current row to ALL other Teams. Without the ALL function, we'd simply be comparing New Zealand to New Zealand and France to France. That's pretty boring! We want to compare New Zealand to France, Republic of Korea, USA and ALL the OlympicTeams. 

The rest of my expression helps ensure that the Rankings will display as I want them to when the user filters to show only Bronze or only Silver medals - this shouldn't change the Gold First Ranking System, so I've added the last line: 

        ALL ( OlympicMedals )

Go ahead and test out the report. Hopefully the USA will bring home a few more Golds so we can avoid the controversy of the two ranking systems, and I'd love to see NZ have another stellar day tomorrow and bring home some Gold to beat France and Republic of Korea. 

Enjoy the Olympics!

Tokyo Olympics: Automate Twitter Posts

 

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: 

1. Connect to live dataset using Power BI
2. 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)
3. Publish Power BI dataset
4. Setup automated, scheduled refresh on Power BI dataset
5. Create dashboard and manage alerts for the two key metrics
6. 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: 

1. Start whenever a new medal has been earned by US (as triggered by alert in Power BI dashboard)
2. Search for Tweets from @TeamUSA on Twitter - limit search to 1 latest Tweet
3. Retweet that Tweet (hope here is that it's related to the medal they've just earned)
4. 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:
1. Go to the SharePoint list of quotes, find the next in sequence and add that text to the Tweet
2. 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)
3. 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!