Collecting data using Microsoft Forms is a common practice, and analyzing the responses in real time is incredibly valuable. In the past, many users relied on the Streaming Semantic Model (Dataset) in Power BI to capture and visualize live data. However, with the recent retirement of this feature, it's time to use Microsoft Fabric Real-Time Intelligence as the new solution for live reporting.
In this blog, I'll guide you through the process of implementing a real-time solution for Microsoft Forms responses using Microsoft Fabric Real-Time Analytics.
You'll learn how to:
- Use Power Automate to collect MS Form data.
- Stream the collected data into Eventstream using a custom source.
- Transform the data and save it into a KQL database.
- Create a live report using KQL data with Power BI.
By the end of this post, you'll have a seamless workflow to capture, stream, and analyze data from Microsoft Forms in real-time using the Real-Time Intelligence in Microsoft Fabric.
Video:
1- Using Power Automate to collect MS Form data
The first step is to create a Flow in Power Automate to collect data automatically from MS Form once users submit their responses. For this blog, I have created a simple form with 3 questions as follow:
Open Power Automate, click on the Create button, and select Automated cloud flow. The "Build an automated cloud flow" will be opened, please add a name for the flow and select the first trigger in the list named "When a new response is submitted (Same as image below) and click on Create:
Now the edit page of the flow opens and you can configure the trigger. Please open the Form Id drop down and
select the name of the Form you have created in MS Forms. Now we can add the actions, for the first action, click on the new step, search for Microsoft Forms, and select the "Get Response Details" action (The only action for MS Forms).
Again, select the Form Id same as previous action and click inside the Response Id field, and then select the "Response Id" which is the output of the previous action:
By using this trigger and action, we are able to automatically collect the submitted responses and now we need to add more actions to send these data to Microsoft Fabric Real-Time Intelligence.
2- Stream the collected data into Eventstream using Custom Source
To stream the data from Power Automate into MS fabric RTI (Real-Time Intelligence), first we need to create an Eventstream, open one of your workspace that has Fabric License, click on the New Item and select Eventstream:
Add a name for your Eventstream, if it ask for "Enhanced Capabilities (preview)", please select that and click on Create.
Add a "custom endpoint" as the source for your Eventstream (You can add any name for this source).
Press publish button and then the custom source is ready to be used by Power Automate for streaming data. Return to the Power Automate Flow edit page, and add a Compose action to transform the data shape into a proper JSON for sending data to the Eventstream.
The data we need are answers of the form's questions and the details of submission, who submit that and when, so the structure of the JSON for this demo need to be as below:
{
"Submitted By": ,
"Submitted Time": ,
"Country": ,
"Visited Before": ,
"Rate Country":
}
and need to map the previous action output's fields to this JSON like follow:
I have explained both the previous and next section "How to Use Power Automate as an Eventstream Source Custom App" in detail in another blog post, and I highly recommend reading it for additional context. You can find it here: https://www.vahiddm.com/post/fabric-real-time-analytics-use-power-automate-eventstream-custom-app
Now, the data is in the good JSON format to sent it to Eventstream. Click on the new step in editing page of the Power Automate flow, search for Event Hubs, and select Send Event to be added to the flow as the new and last action:
After adding the Send event action to the flow, you need to set a new connection for that to be connected to the custom source in the Eventstream. Please read this blog post as I explained step by step how to set the connection, link: https://www.vahiddm.com/post/fabric-real-time-analytics-use-power-automate-eventstream-custom-app
Video:
Once the connect is fully configured, then add the output of the Compose action into the Content filed of the Send Event:
The flow is now fully ready for testing. Open your MS Form and submit with some data as a user. Check the flow run history and make sure it has been completed successfully:
Open the Eventstream and check the Data Preview (if it's blank, click on refresh button):
The above image shows that the data received successfully to the Eventstream and now we can transform and save data.
3- Transform data and save them into a KQL database
We created the flow in Power Automate and it stream data automatically into the Eventstream. Now, we need to change some fields data type and save them into the KQL database. In the Eventstream, open the Transform Events add destination drop down and select manage fields:
Once it's added to the Eventstream, click on edit, and select all fields you want to save into the database:
The data type for Submitted Time needs to be changed to DateTime and Rate Country need to be changed to Int64, so select all fields you need and set the data type for them, and then click on Save button:
Now data is ready and you need to create the KQL database. To create and manage KQL databases, you need to have an Eventhouse. Open the workspace and create an Eventhouse:
When it's ready, you can see a KQL database with the same name of the Eventhouse has been added to it, and you can use it to save your data into it (you can also create another KQL table in this Eventhouse to be used for this solution). Back to the Eventstream and select the Eventhouse as the destination after the Manage Fields, and set it up as follow:
- Destination name: any name you like to assign to this destination
- Workspace: select the workspace that you created the Eventhouse there
- Eventhouse: select the Eventhouse here
- KQL Database: Select the name of your KQL database
- KQL Destination table: Click on the create new and add a name to that table
- Click on Save
Make sure to click the Publish button after configuring the Eventstream to save your settings and activate the data flow.
Your Eventstream is ready! Click on the Eventstream destination, select Test Result, refresh, and ensure the data appears as expected:
Now, the data is ready for analysis and visualization in Power BI.
4- Create a live report using KQL data with Power BI
To create a Power BI report with DirectQuery connection to the KQL table, you can directly create that inside the Power BI Desktop or create that in Power BI Service / Fabric (I will use the second option).
Open the Eventhouse and select the KQL database that you used to save the data into it. Select the table, open the options and select the Power BI:
Create your report and save it.
Once the report is created in the Power BI service, you can download the PBIX file to your local machine and continue developing it in Power BI Desktop. This enables you to enhance visuals, add calculated measures, and further refine the report.
Now the solution is fully developed, and you have a Power BI report that displays Microsoft Forms responses in real time, providing live insights and enabling immediate analysis.