If you’re using Microsoft Forms you can create a dataset in Power BI to collect the feedback or information from form submissions. This can then be used in your reports and dashboards for your reporting needs. Using Power Automate, each time someone submits a response to your Form, the data will be stored a a Power BI dataset.
1. Create a dataset in Power BI Service
Navigate to your workspace in Power BI https://app.powerbi.com/. Select +New > Streaming Dataset.
Select API as the source of your data, and click Next. You’ll have to give the dataset a name. Name it what you like, such as the name of the Microsoft Form you are collecting data from.
In the Values from stream area, enter in a value for each of the questions you want to store the data for. If you also want to store the date the form submission was made, add a value for the submitted date.
The screenshot below shows my form with 3 types of questions- free text, choice and rating. Beside it are the values I have added in the dataset, and the corresponding data type selected for each.
- Free text and choice should be stored as Text
- Ratings should be stored as Number
- Dates should be stored as DateTime
Make sure to turn Historic data analysis ON, then click Create.
The dataset is now created, but is currently empty. You will use Power Automate to send Microsoft Form submissions into the dataset, so that you can build reports and dashboards on the submissions collected.
2. Create flow to send MS Form submissions to Power BI dataset
In another browser tab, go to Power Automate https://make.powerautomate.com/. Create a new Automated Cloud Flow from blank, using the Microsoft Forms trigger, When a new response is submitted.
In the trigger, select your the name of your form in the Form ID field. Add another Forms action underneath- Get response details. Select the same form as the Form ID, and select the Response ID dynamic content for the Response ID field.
If you also want to collect the date your form was submitted in your dataset, you may wish to convert the time zone. This way, the date will be stored in your dataset according to your local time. Add the Convert time zone step.
- Base time: Select Submission time dynamic content
- Source time zone: (UTC) Coordinated Universal Time
- Destination time zone: Select the time zone you wish to use (e.g. your local one)
- Format string: Select one either with just a date, or date and time
For the last step in your flow, add the Power BI action Add rows to a dataset. Specify the Workspace you created the dataset in earlier. Then, you should see the dataset name appear in the Dataset dropdown. Table will be RealTimeData.
The fields you created earlier will appear in the Power BI action. Now, you just need to select the corresponding Forms dynamic content for each field (and if you are using the Form submission time, select the Converted time).
Save your flow.
3. Test it out
Now that your flow is saved, go to your form and make a few submissions to check that it is working.
Go back to your Power BI browser tab. In the workspace, you will see the dataset you created earlier listed. Click on the … next to it, and select Create report.
From the Fields area, expand RealTimeData. You’ll see that the fields you created for your dataset are listed. As you use these fields in your report, you’ll notice that you can see the values you just submitted as you tested out your form. You can now use this dataset for your reports and dashboards built in Power BI.
Rishona Elijah 