How to Connect MS Forms to Power BI using Forms for Excel

Article: Connecting MS Forms to Power BI

Microsoft Forms is a common tool used to collect survey data. While it is an awesome tool for data collection, a better choice for reporting and visualizing the data is to use Power BI.

As there is no Power BI connector for Microsoft Forms, we can use a workaround that uses the Web connector.

This method creates what is considered a Group Form in MS Forms by using the Forms for Excel option in SharePoint. This means that anyone with access to the SharePoint Group that the Form is created in will have access to the Responses in the Excel file. This is an updated version of our Importing Microsoft Forms to Power BI blog.

Before creating the Form, you should consider what kind of information is being collected, and who in your team should have access to this information. If you wish to connect to a private survey, check out our other tutorial that uses a Power BI Streaming Data Set.

Note, this method also assumes that you have not already created your Survey in Microsoft Forms and will be creating one from scratch. If your form has already be created, you can transfer ownership to a group without having to recreate the form. This will create the Excel file in SharePoint so that you can connect Power BI to that Excel file. Find this file by opening the response in Excel on the responses tab after transferring ownership. This way you do not need to set up a streaming data set and you do not need to recreate (and potentially) lose the form responses.

Contents

Step 1 – Create the Forms for Excel in SharePoint

Go to the SharePoint site you wish to hold the data in and select Documents from the navigation menu on the left-hand side.

Homepage of a Sharepoint site/group. Navigation menu is on the left hand side with Documents third from the top.
Figure 1 Documents

In the Documents tab, open the folder you wish for the responses to be stored. If you do not have a folder already, create one in this step by clicking the + New dropdown.

The +New drop down menu, select the first option
Figure 2 Create a folder.
Open the folder that will hold the create the forms to excel survey in.
Figure 3 Open the folder.

Now that you have the folder opened, we will create the Form.

In the + New menu, select Forms for Excel

The +New drop down menu, select the first option
Figure 4 Select Forms for Excel

A window will appear asking you to name the Excel file.

Enter a name for the Excel file.
Figure 5 Enter File Name

After naming the file, a new browser tab will open and you will be taken to a blank form in forms.office.com.

By creating the survey through SharePoint Groups, an Excel file is now located in the Documents folder that you selected. This is where the responses from your survey will be automatically stored.

Example showing the forms response output excel file in the folder.
Figure 6 Form Response Output File

Step 2 – Create Your Survey.

The next portion will be to create the questions needed in your survey. As this portion will vary for every instance of using MS Forms, I thought it would be more beneficial to provide you with an overview of the question’s types in MS Forms.

For best practices in survey creation, I recommend this article by SurveyMonkey. If you already know about MS Form question types, skip to Step 3 here.

For fun, I have inserted the survey that I created in this tutorial for you to fill out.

Question Types in Microsoft Forms

Microsoft Forms Question Types: Choice, Text, Rating, Date, Ranking, Likert, File Upload, Net Promoter Score
Figure 7 Question Types in MS Forms

Choice

Picture of a multiple choice question in MS Forms.

Question: What is your favourite kind of pizza?
Choices: Cheese, Hawaiian, Pepperoni, Other
Figure 8 Multiple Choice Question

The first question type in MS Forms is Multiple Choice. This type allows the respondent to select from an option set created by you.

One feature here is the “Other” option, with allows the respondent to type in an answer different from the one provided.

Another feature is that the survey creator can specify if they wish to allow more than one answer. 

In the more options, the form creator can specify if they wish for the options to be shuffled and if they would rather it be a drop-down list.

Text

Picture of a text question in MS Forms.

Question: what is the weirdest combination of pizza toppings you can think of?
Figure 9 Text Question

The second question type is called Text. This type allows for short or long answers typed out by the respondent. The character limit for a short answer is approximately 1,600 and for a long answer is 4,000.

The restrictions option allows the survey creator to restrict the text input to only allow numbers, and further restrict that to a range of numbers.

Rating

Picture of a rating question in MS Forms.

Question: On a scale of 1 to 19, how much do you like pizza?

Scale: 1 - Hate it, 10 - Love it
Figure 10 Rating Question

The rating question asks respondents to select where they fit on a scale based on the question. The scale can have up to 10 levels and MS Forms gives the option to either have a number or star symbol.

In the more options, you can add labels for the lowest and highest options, as seen in Figure 10 with 1 labeled as “Hate It” and 10 labeled as “Love It”. Adding labels is generally considered the best practice for rating questions. 

Date

Picture of a date question in MS Forms.

Question: When was the last day that you ate pizza?
Figure 11 Date Question

The date question allows the respondent to select a specific date from a calendar.

Ranking

Picture of a ranking question in MS Forms.

Question: How would you rank the following list of pizza types in order from most preferred (top) to least preferred (bottom)?

Choices: Cheese, Hawaiian, Pepperoni, Deluxe, Vegetarian
Figure 12 Ranking Question

The ranking question type asks the respondents to rank a list of options. It is up to the survey creator to specify the context of the ranking.

For example, in Figure 12, the respondent’s most preferred pizza should be listed first.

The respondent will be able to move the list items using up or down arrows. The order in which you list the items for the question will be how the respondent sees them before organizing the answers themselves.

Struggling with your analytics journey?

Let Iteration Insights get you on the right path, view our services to see how we can help you today.

Likert

Picture of a Likert question in MS Forms.

Statement: Eating pizza is my worst nightmare, I could eat pizza for a year

Scale: Strongly Disagree, Disagree, Neutral, Agree, Strongly Agree
Figure 13 Likert Question

A Likert question asks the respondent to make a selection based on a statement. This is most often used to gauge attitudes and opinions about a topic.

File Upload

Picture of a file question in MS Forms.

Question: Please upload a picture of your favourite pizza.
Figure 14 File Upload

MS Forms allows for file uploads. With this type, the survey creator can specify what type of file they will be allowed to upload along with the total number limit and file size limit.

If you are looking to collect file uploads from individuals outside of your organization, you must make sure that your organization allows for external sharing.

Net Promoter Score

Picture of a net promoter score question in MS Forms.

Question: how likely are you to recommend pizza to a college or friend?
Figure 15 Net Promoter Score

The last question type is Net Promoter Score (NPS). An NPS is a metric used to determine how likely the respondent is to recommend a particular item to a third party. The NPS is calculated based on the percentage of Promoters minus Detractors. Learn more about NPS in Microsoft Forms.

Where is my form located?

If you wish to find your form after creation, you can locate it by going to forms.office.com. It will be located under the My groups section and then within the group that you selected in SharePoint in Step 1.

A picture of the My groups section on the homepage of the forms.office.com website.
Figure 16 My groups

Step 3 – Locate and copy the file path for the Forms response output file

Now that you have your survey, we can connect the forms response Excel file to Power BI using the Web connecter data source.

Locate the Excel file in SharePoint from Figure 6.

The folder that hold the forms response outout file in sharepoint.
Figure 17 Forms Response Output File

Open the file in the Excel Desktop App by selecting the ellipses then Open, and then Open in app.

Select the ellipses, select the first option
Figure 18 Open in Excel

In the Excel file, in the top left corner go to File > Info > Copy Path.

In the top left hand corner of the excel file, select the
Figure 19 File tab
From the navigation menu on the left hand side of the excel file, select the fourth from the top option
Figure 20 Copy Path

Close the Excel file.

Step 4 – Connect the MS Form Responses File to Power BI through Web connecter

Open Power BI Desktop. In the ribbon, select Get Data > Web.

In Power BI Desktop ribbon, select the
Figure 21 Get Data

In the From Web window, paste the file path into the URL field and remove everything that comes after .xlsx and select OK.

In the From Web Pop up window, paste the file path copied from the excel file in the previous step.

Remove anything that comes after.xlsx in the file path and select OK.
Figure 22 Paste file path and remove ?web=1

From here Power BI will ask you to sign in using your SharePoint credentials.

Once connected, the Navigator window will appear. Select Form1 and click Transform Data to open Power Query and start working on the data.

in the navigator window, on the left hand side, select the
Figure 23 Select Form1

Congratulations, you have now connected your survey to Power BI!

For more information on how to model survey data check out this DAX Patterns blog by SQLBI.

Conclusion

I hope that this tutorial on how to connect a Group Form from MS Forms to Power BI was helpful for you. Please keep in mind that this method creates the survey and stores the responses in a Group in SharePoint. This means that anyone with access to the group can view and modify the form and responses. Feel free to fill out the survey that we created in this tutorial here.

Stay Up to Date with Our Latest Blogs

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

8 Comments

  • Any advice on how to get the data to allow aggregate options? I have spent some time Googling and have tried editing the fields in the Excel sheet to ‘text’ and ‘number’ but I still only have ‘Count’ & ‘Count (Distinct)’ as my options. I am looking to calculate totals and averages of live values coming in from the form.

    Thanks!

    Reply
    • Hello Casey,

      In order to do summarize numbers, you will need to ensure the column has the correct data type.

      In Power BI Desktop, in the Data view on the left-hand side. Go to the table that has the data you are looking at. In the ribbon, you will see a Data Type drop-down option. Select either whole number or decimal number.

      Data Type

      Hopefully, this helps! Let me know if you need further clarification on this.

      Best,
      Renata

      Reply
  • Hello, how quickly does this data update in Power BI once form responses are submitted?

    Reply
    • Hi Jacob,

      It can really depend. In Power BI Desktop, when you refresh the data source, the new submissions should be available immediately.

      In Power BI Service, a connected dataset can be updated on-demand at a specified schedule you set. How long the refresh takes depends on a couple of factors such as how much data is being loaded in, Microsoft pulling the data, time of day (if everyone is refreshing at the same time). You will have to test out to see how long it takes for your particular data set and the rate could change by a margin of 50% each time you refresh.

      For more information on refreshing data sets, check out this Microsoft documentation about it.

      Renata

      Reply
  • Is it possible to take an existing stand-alone Forms form and connect it to a new Excel for Forms file that can be used by Power BI directly rather than having to export an Excel file manually from Forms?

    Reply
    • Hello Witold,

      You can transfer ownership of the form to a group that would then allow you to follow the tutorial from Step 3 on. Here is a link to how to change ownership.

      If it is not possible to have a Group own the form, then you can connect to the data using a Streaming Data Set.

      Hope this helps,
      Renata

      Reply
  • Is there any way to edit / add fields to a form created this way after the fact? Or do I have to start over with a new form?

    Reply
    • Hello Rob,

      You should be able to edit the Forms as you normally work in Microsoft Forms. If you create a new field after the fact, a new column in created in the excel sheet where the new data will be collected.

      After updating the form and collecting responses, you will just need to ensure to to refresh the data in Power BI.

      Hope this helps,
      Renata

      Reply

Leave a Reply to Jacob Cancel reply

Your email address will not be published.

Renata Kalsbeek is a Business Development Coordinator of Iteration Insights. Renata graduated with a B.Comm from Toronto Met, where she completed a double major in Marketing and Economics within 3 years. She is passionate about marketing strategy and planning and has developed an interest in leveraging marketing automation to run a lean marketing team.

Iterate With Us

Signup for the Iteration Insights newsletter for access to recent news and other updates.

Related Posts

Share on

Next Event

Let's get started with a consultation

get started with training

Sign up for our newsletter