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."

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.

form title

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."

form question choice

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

microsoft forms add option

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

add new date

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:

microsoft power bi workspace

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

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

microsoft powerbi streaming dataset

Select "API" and click on "Next."

power bi streaming dataset api

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

microsoft power bi streaming dataset

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 and logging in with your credentials.
  2. In the upper left corner of the window, select the app launcherapp launcher
  3. Select the SharePoint icon.sharepoint icon

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

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":

sharepoint site

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

new list

Select "Blank list."

Name your list and select "Create."

sharepoint list title

Now, click on "Edit in grid view."

edit sharepoint list

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.

sharepoint list columns

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:

Microsoft power automate cloud flows

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

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!

pick a form

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

form id

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

get response details

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."

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).

dynamic content

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.

powerbi dataset

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."

preview form

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:

power bi workspaces

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."

power bi create report

Now we are at the report builder.

microsoft power bi 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.

power bi 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."

change title

This is now reflected as the title of the visual.

power bi report title change

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

power bi report 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.

microsoft team

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

add a form to microsoft teams channel

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

steps tracker form

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

form added to microsoft teams channel

Now for the real-time dashboard of results.

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

create a new tab in microsoft teams 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.

Add power bi report to microsoft teams channel

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

power bi report 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."

edit power bi report

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

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

historic data analysis in power bi

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."

delete responses in Microsoft Form

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."

delete form responses in sharepoint list

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.