How to Automatically Import Microsoft Forms Responses to Power BI

photo showing the process of going from ms forms to sharepoint to power bi service to power automate to power bi desktop to ms teams

Table of Contents

Iteration Insights has been running the Calgary Power Platform User Group (formerly Calgary Power BI User Group) since the end of 2018. However, as of April 2020, the meetings moved to virtual delivery because of COVID-19.

Similarly to many other user groups, this transition presented a challenge of how to interact with members and keep them engaged. We decided to include polls to keep engagement up along with an opportunity to help us better understand our audience, and their areas of interest.

Surveying the group provides us with direct feedback to help our team manage the user group and make better decisions regarding topics in the future.

Our tool of choice is Microsoft (MS) Forms to collect the Survey data, and then to use Power BI to visualize the survey results allowing for real-time updates as responses come in. While there are many direct connectors in Power BI, MS Forms does not have one.

To solve this problem, we utilized Power Automate to automate the process of importing the data to Power BI.  This would use a steaming data set to import the data to Power BI and we will also use a SharePoint list to keep a master record store of the data collected from Forms.

Then we created the report in Power BI Desktop by connecting to the streaming dataset.  Lastly, upon completion of the report, we connect it to the appropriate channel within MS Teams.

Here is the tutorial on how to go about this.

Need help with Power BI?

Let us talk about how Iteration Insights can improve your Power BI experience. From training to solution delivery, we got you covered.

Step 1 – Create Microsoft Forms Survey

To create a survey in MS Forms go to forms.microsoft.com and choose New Form.

At the top of the page give the survey a Title, we will use Survey Demo. Below this, click + Add new to add the first question. Choose Choice as the question type for the three Demo questions.

Enter the following questions and choices or feel free to make up your own.

  • Question 1: Is this your first time attending a Calgary Power Platform User Group Meeting?
    • Choice: Yes/ No
  • Question 2: How did you find out about this event?
    • Choice: Calgary Power Platform User Group Site/ Twitter/ LinkedIn/ Word of Mouth/ Other
  • Question 3: How long have you been working with Power BI?
    • Choice: 1 – 6 month/ 6 – 12 months/ Over 1 year/ I do not use Power BI

Click + Add Option to add more choices to the question.

Click + Add New to add New Question.

how to add another option to a choice question in ms forms

This is what your survey should look like.

example of the survey created in ms forms

Once you have created the questions in forms. Now we need to create a SharePoint List to store the survey data.

Step 2 – Create a SharePoint List to store the survey data

Go to the SharePoint site where you would like the list to live, we are using our PPUG Demo site and create a New List, enter a List Name, we are using Survey Demo.

how to create a new list in sharepoint

Once the New List has been created, it is time to add new columns to the list. You must create one column for each of your questions, in this case, we will be making three columns.

Click on + Add column, you should see a dialog box pop up on the right side.

Fill in the form as such:

Name: Description of the Question

Type: Single line of text

  • Note, because the data from Forms displays in text format you can create all your columns with a single line of text type, regardless of whether it is a number or date.

Description: Question from the survey

Do this with the three questions and name the columns: First Time AttendingEvent Source, and Power BI Experience (to store Survey results).

how to fill out the column forms in sharepoint

This is what your list should look like.

howcases the Sharepoint columns you need to match you ms forms questions

Now that the SharePoint List is ready, let’s create a Streaming Dataset in Power BI Service.

Step 3 – Create a Streaming Dataset in Power BI Service

Go to powerbi.com. Locate the workspace where you would like your dataset to live. I am using My workspace to create a streaming dataset.

In My workspace, click + New and then choose Streaming dataset.

how to create a New Streaming Data Set in power bi

Choose API as the source of your data, and click Next.

how to create the streaming dataset in power bi

In the New streaming dataset dialog box, enter Survey Demo as the Dataset name, enter Values from stream as shown in Figure below. Turn on Historic data analysis, then click Create.

The new dataset, Survey Demo is now added to My workspace.

how to fill out the streaming dataset form in power bi

Step 4 – Create a Flow to automate the data import process

Now that the SharePoint List and the streaming dataset are ready, let us create a flow to automate the process of populating them with data.

Go to flow.microsoft.com, click + Create on the left-hand navigation pane.

Choose Automated flow.

power automate menu
Automated Flow button in power automate

In the Build an automated flow window, enter Survey Demo as the Flow name.

Choose When a new response is submitted as the flow’s trigger. If you cannot see the option, type the trigger name in the search bar.

Click Create.

how to create a flow from scratch in power automate

This will populate the flow with the trigger ready. With-in the trigger, in the Form Id drop-down list, choose the survey you created in Step 1. Ours is called Survey Demo.

Then click + New step.

shows the flow trigger when a new response is submitted from ms forms

From the Choose an action Search bar, enter Get response details. This will allow you to populate the flow with Dynamic Content from the survey.

choose an action window showing how to find the ms forms action

Under the Get response details step, choose Survey Demo as the Form Id.

For the Response Id, select List of response notifications Response Id from the Dynamic Content button in the bottom right corner.

Then click + New step.

shows how to add dynamic content to fill in a power automate action

In the new step, type Create Item in the search bar, select the one from SharePoint. Configure as the following using Dynamic Content. Using Dynamic Content will allow the list to populate with the responses from the Form. Once completed, click + New Step.

  • Site Address: PPUG Demo
  • List Name: Survey Demo
  • Title: List of response notifications Response Id
  • First Time Attending: Is this your first time attending a Calgary Power Platform User Group Meeting?
  • Event Source: How did you find out about this event?
  • Power BI Experience: How long have you been working with Power BI?
how to fill out the create item in sharepoint action in power automate

Once finished, click + New Step and type Power BI in the search bar and choose Add rows to a dataset.

search example to find the power bi action for connecting to the streaming data set

Configure as the following,

  • Workspace: My Workspace
  • Dataset: Survey Demo
  • Table: RealTimeData
  • Date: Created
  • First Time Attending: First Time Attending
  • Event Source: Event Source
  • Power BI Experience: Power BI Experience
  • Respondent ID: List of response notifications Response Id
example of how to fill the add rows to data set power automate action

Once completed, click Save.

The Survey Demo Flow should look like the following.

example of the completed flow created in power automate

Now that we have the steps in place, let us test this flow to ensure it is working properly. In the top right corner, click on Test and choose I will perform the trigger action. Then Test.

the Test flow window in power automate

Then go to the survey you created in Step 1 and submit a response. Switch back to the Flow page, a notification shows Your flow ran successfully.

example of the power automate flow successful run

Step 5 -Create Report in Power BI Desktop

Now that you have the MS Form survey connected to Power BI, you can build your report from Power BI Service or Desktop. I prefer to build the report in Power BI Desktop, so I will walk you through that process.

Open Power BI Desktop > navigate to Home > Get data > Power BI datasets.

drop down of the get data menu in power bi

Choose Survey Demo from the list, then click Create.

view of the dataset selection window in power bi

When the connection is built, you should see a table name RealTimeData with 5 columns: DateEvent SourceFirst Time AttendingPower BI Experience, and Respondent ID.

From here you can build and configure your report as needed.

power bi desktop view, highlighting the table menu on the right

Here is what our Survey Demo Dashboard looks like. Save the Report, then Publish it to the service – My Workspace.

power bi survey dashboard

Step 6 – Share and collaborate in Teams

Lastly, it is our preference to access our Power BI reports through MS Teams. As such, we need to share this report with the appropriate channel in Teams.

Locate the channel you wish to access the report ours is in PPUG Demo > General channel. In the top menu, click the icon to add a new tab to the channel. In the Add a tab window, choose Power BI.

In the Power BI window, select My workspace, and then Survey Demo Dashboard.

Click Save.

Press the + key to had the power bi dashboard to ms teams

The dashboard will now be displayed in the menu at the top of the channel like so. We hope this helped you and your team out in our #newnormal. Stay tuned for more tips and tricks!

View of the Power BI dashboard in MS Teams

Stay Up to Date with Our Latest Blogs

Subscribe to our mailing list to be notified when a new article is released.

Share This

Share on linkedin
Share on twitter
Share on facebook
Share on email

4 Comments

  • Thank you so much, this was very helpful! I have an issue I’m hoping you could potentially help with-When I get the data from the flow into Powerbi, I see that I am not able to see the data in “Transform data” nor am I able to format the data. I have a “zip code” field, which I want to use in a map, but I’m unable to because the format is not geo-coded.

    Reply
    • Hi Whitney,

      Thank you very much for your comment!

      When connecting to a live dataset or a dataset in Power BI service, you lose the ability to use the data and model tab as well as the transform data feature. For more information on the limitation, you can go here: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-report-lifecycle-datasets#limitations-and-considerations

      Based on the information from your comment, we concluded the following:
      1. When connected to a streaming dataset, if the ZipCode is in its own column it can be still be used within the Bing Maps visual even if set as text.
      2. If transformation is necessary, you will have to connect the Power BI file directly to the SharePoint list. Unfortunately, you will no longer have streaming data and will have to resort to regular report refreshes.

      I hope this helps! If you are still having issues, feel free to reach out to info@iterationinsights.com.

      Reply
  • I would like to thank for the detailed step by step explanation with screen shots, i have followed the same and was able to create with out any flaws.

    Yes as mentioned above there are certain limitations which seems to be ok, as the main purpose is to have live data.

    Thanks once again.

    Regards,
    Pavan

    Reply
    • Thank you for your comment, Paven!

      We are thrilled to hear that the solution worked for you.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Latest Posts

Hot Topics

Stay Up to Date with Our Latest Blogs

Subscribe to our mailing list to be notified when a new blog is released.

Let's get started with a consultation

get started with training