There are many ways to leverage Microsoft’s cloud suite to provide real-time data analysis.

For this tutorial, we will use a simple example. Let’s say your company decides to hold a fitness contest to encourage everyone to be more active on a daily basis. Employees are organized into teams that will compete by entering the quantity of steps they individually take on a daily basis. Management wants to have an easy way for everyone to submit their steps daily as well as an automated leaderboard for everyone to see their team’s progress.

Now, we could use email polls or track this all manually in an Excel spreadsheet, but if you are a Microsoft 365 customer, why not eliminate all that manual process, leverage your existing software, and make most of the project automated?

We are going to use the following Microsoft tools to accomplish this:

• Microsoft Forms
• Microsoft Power BI
• Microsoft SharePoint Lists
• Microsoft Power Automate (Microsoft Flow)
• Microsoft Teams

Since this is a team contest, we will want to know the team the individual is on, the steps submission date, the quantity of steps, and we will also record their name (but not include it in the reporting as it will be only used if we need to correct any entries made in error).

So, the fields we want to capture are:

1. Team Name
2. Date
3. Quantity of Steps
4. Full Name

Let’s dive in and create a real-time dashboard from Microsoft Forms data.

Step 1: Microsoft Forms

We are going to start this project by creating a form for users to work with.

Log in to Microsoft Forms.

Click on "New Form."

It’s important to note that whatever we name the form as its "Title" will also be its name (e.g., like a file name). Let’s name this example: Steps Tracker 3000.

Now it’s time to add questions to the form. Remember to mark each question as required to ensure all necessary data is obtained for the contestants.

Click on "Add new" and select "Choice."

Add your selection options, clicking on "Add option" after each finished option until all desired options are shown:

Now click on "Add new" and select "Date." Name the question:

Click on "Add new" and select "Text."

That’s it, that’s all we need for this simple form. We will obtain the name by using the submitters email address.

Step 2: Microsoft Power BI

Now let's connect our new Microsoft Form to the dashboard we want to create.

Log in to Microsoft Power BI.

Now, click on "Workspaces" and select the "Workspace" you want to create your resource in:

In this example, I will use the "Dev" workspace.

Click on the "New" dropdown menu and select "Streaming dataset."

Select "API" and click on "Next."

Now name your new streaming dataset and create the Values that you want to include in your real-time dashboard for the contest.

Note: Make sure to enable "Historic data analysis."

Important: The values you create for the stream should align with the data you will be receiving from the Form created in Step 1. For this example, the following aligns with our Steps Tracker 3000 data.

Click on "Create" once finished and then click on "Done."

Step 3: Microsoft SharePoint List

Log in to SharePoint by

1. Visiting office.com and logging in with your credentials.
2. In the upper left corner of the window, select the app launcher
3. Select the SharePoint icon.

Note: If you don't see the SharePoint icon, use the Search option near the top to search for SharePoint.

Now that you are logged into SharePoint, select the site that you want to create this List under. In this example, I will use the site "Dev":

Click on the "New" dropdown menu and select "List."

Select "Blank list."

Name your list and select "Create."

Now, click on "Edit in grid view."

Leave the "Title" column alone and click on "Add column."

Name the first column "Name" and click on "Save."

Repeat these steps to create columns for Team, Date, and Steps.

Alright! Now our list is created. We will use this if any teams have questions on when their last entry was or if we need to review submissions to make a correction.

Note: The List data does not flow to the automatically updating leaderboard; this is for reference as a mirror dataset of the information flowing into Power BI.

Step 4: Microsoft Power Automate (Microsoft Flow) – Creating an automation flow

Now we'll start on the automation portion that will send Microsoft Forms data into the dashboard. Start by logging into Microsoft Power Automate.

You should see the following:

Select the "New flow" drop-down menu and click on "Automated cloud flow."

From here, name your Flow something unique and related to your data analysis project.

Now we need to select which trigger event will start the automation workflow. For this example, we will select "When a new response is submitted" via Microsoft Forms.

Click on "Create" and let’s move on!

From the "Pick a form" menu, select the Form we created in Step 1.

Now click on "New step" and type "forms" in the search bar. Click on "Get response details" to be the next action.

Click on the "Form Id" field and select the Form created in Step 1.

Then click on the "Response Id" field and select "Response Id."

Now click on "New step" and type "create item" in the search bar. Click on "Create item" (SharePoint) to be the next action.

Select the SharePoint site where your list was created (Dev in this example).

Then select the List Name.

For each value below, click on the field next to it and select the relevant Dynamic Content item (that will pull the information from your form and write it to the list under the corresponding column).

Now click on "New step" and type "power bi" in the search bar. Click on "Add rows to a dataset" (Power BI) to be the next action.

Select the Workspace where your streaming dataset was created in Step 2 ("Dev" in this example).

Select the streaming dataset.

Select "RealTimeDate" as the Table.

Note: The fields you created for your streaming dataset should now show up with fields for you to add items to.

Select the corresponding Dynamic Content items (make sure to select the Forms items) for each field.

By selecting the field by "Name," I have the option to select an item from the Dynamic content menu. You want to select the corresponding item — this example — I would select "Name" and move onto the next field.

Click on "Save."

Step 5: Microsoft Power BI – Report

Now that we have all the components ready, we need to create some test data and then build the leaderboard.

Go back to Microsoft Forms (if you left the tab open in your browser, nice call — saves time).

Click on the Steps Tracker 3000 form and click on "Preview."

You can see your finished form. We need test data, so complete the form several times for different teams in order to generate test data for us to work with.

Once you have created enough test entries, we are going to head back to Power BI to create our dashboard.

So, head back to Microsoft Power BI.

Click on "Workspaces" and select the Workspace you want to create your Leaderboard in:

In this example, I will use the "Dev" workspace.

After clicking on your Workspace, you should see a list of resources, including the Steps Tracker 3000 dataset.

If you click on the 3 dots, left of the "Dataset" text, you will see a dropdown menu. Click on "Report."

Now we are at the report builder.

I started by naming the tab Leaderboard 1 and then creating a new tab labeled Leaderboard 2.

On the first tab, I went a simple horizontal bar chart.

Notice the name of the visual says "Steps by Team." Let’s change that to "Steps Tracker 3000 – Leaderboard 1."

Click on the paint roller icon and select the "Title" dropdown. Here, change "Steps by Team" to "Steps Tracker 3000 – Leaderboard 1."

This is now reflected as the title of the visual.

Let’s click on the second tab and make the same changes, but with a simple pie chart.

Play around with the formatting and visualization options until you have the leaderboard style that you want for your project.

When ready, click on "Save," name your report, then click on "Save" again.

Step 6: Share in Microsoft Teams

Now that we have a report saved, we can embed it in Microsoft Teams so that all team members can see the report and the submission form easily.

In Microsoft Teams, click on the Team you want the resources to be available in, then click on the channel where you want to house the form and leaderboard. For this example, the Team is "Dev" and the Channel is "Automation Testing."

Click on the + icon to create a new tab in this channel.

Type "Forms" in the search bar and click on "Forms."

Select "Steps Tracker 3000" and click on "Save."

Now the submission form is available to anyone with access to this Teams group and channel.

Now for the real-time dashboard of results.

Click on the + icon to create a new tab in this channel.

Type "Power BI" in the search bar and click on "Power BI."

Name your tab "Steps Contest Leaderboard" and select the Power BI report we created.

Click on "Save" and now the automatically updating leaderboard report is available in Teams.

Awesome, the project is complete! But wait – there is still test data in the Power BI Report, and we need to clear that out. It's easy to do so. Go back to Power BI and select the workspace we were in (Dev).

Navigate to "Edit."

Toggle "Historic data analysis" to off and click on "Done."

Then repeat, this time enabling "Historic data analysis" and clicking on "Done."

This effectively resets any data in the Power BI streaming dataset. Now the whole workflow is ready for live data.

Lastly, a few housekeeping items: Let’s clear the test data responses in the Microsoft Form.

Click on the Microsoft Form Steps Tracker 3000, then click on responses. Click on the 3 dots and select "Delete all responses" and confirm with "Delete."

Last stop, the Microsoft SharePoint list. Navigate back to the SharePoint site where your Steps Tracker 3000 list is and select all the entries and click on "Delete."

Alright, now we are done!

Teams can enter their steps daily using the Form available in Microsoft Teams and view their real-time progress using the Power BI Report available in the same channel in Teams. Do the work up front and then let the process automation run the contest!

This real-time contest leaderboard is just one example of what can be done to automate business processes when you leverage existing software like Microsoft 365 at your company. You can achieve similar outcomes with larger datasets and other use cases too.