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

30 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
  • Hi,

    Thank you very much for your clear explanation. I am able to follow every step and get the result. I am stuck with two things.
    1. The Date is showing Date & Time Stamp. Is there a way to convert its format in Power BI Report?
    2. I am also collecting the Hours spent in Numbers, those numbers are reported with 2 decimals. Is there a way to fix it?

    Reply
    • Hi Abdullah,
      Streaming DataSets in Power BI have a limited set of Data types and all come with fixed formats. They also cannot be transformed.
      One solution could be to import the data directly from your previously created SharePoint list as this gives you full transformation powers.
      This video presentation of this article shows this in action.

      Hope this was helpful.
      Victor

      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
  • Thank you for the information, it was truly useful!

    I have a question, when I shared my dhasboard on teams, other members could not access the information. Is there some permissions settings that needs to be modified in order to solve this?
    Thank you in advanced for your help.

    Reply
    • Hello Jose,

      You dashboard/report probably is being hosted in App workspace on Power BI service and your Team members do not have access to the workspace.

      There 2 options:
      1. Add all your members from Teams in the App Workspace on Power BI service. You can set them up with “Viewer” role. “Viewer” can access all the reports in the workspace, but not able to edit or create further reports in the workspace.
      2. Republish the report to the Teams workspace. This way, all members in your teams will automatically get the access.

      The option you choose, will depends on how you would like to manage your Power BI reports/workspaces in your organization.

      Hope this helps!

      Tutu

      Reply
  • Hi there.
    How would I go about this process if the form is shared with me, and I didn’t create it myself?

    Reply
    • Hello Matthew,

      Ownership is a tricky thing with Microsoft Forms! There is not a straightforward answer to this but while researching this question I found a potential solution. Please let me know if this solution works for you.

        1. Have the owner of the form transfer the ownership to a group. Tutorial
        2. Group forms are not supported in the MS Forms connector at this time, meaning you will not be able to see the form in the “When a new response is submitted” trigger drop-down list. You can potentially work around this by pasting the Form Id from the URL as a Custom Value (can be chosen as an option in the drop-down list) into the Form ID field. The Form Id value will come after the FormId= in the URL for the form.

      Form Id Example

      From here you should be able to follow the tutorial as normal. Once again, please let me know if this solution worked for you.

      Cheers,
      Renata Kalsbeek

      Reply
  • Thank you for the accurate tutorial! Awesome. It worked on my business case.

    Reply
  • Hi, Thanks for a nice setup, But how do i clear the report so i can start from scratch with real live data?
    When i did set this up i just used test data for input.

    Reply
  • An outstanding share! I’ve just forwarded this onto a colleague who had been doing a little research on this.
    And he in fact bought me lunch due to the fact that I found it for him…
    lol. So allow me to reword this…. Thank YOU
    for the meal!! But yeah, thanx for spending some time to talk
    about this matter here on your web page.

    Reply
    • We are happy to hear that the how-to was helpful for you and your colleague! We hope the lunch was good too.

      Best,
      Renata Kalsbeek

      Reply
  • Hi Tanya, thanks for this – I had figured out how to do it by cobbling several other tutorials. One issue I am having is that the API tool is limited to 75 fields, and my form has over 100. Do you know of a workaround?

    Reply
    • Hi Sherry,

      You’re definitely running up against the limits of the Power BI streaming datasets.

      If you have that many fields, you may wish to explore other data models for performing a survey. I would also revisit the business value of having a streaming dataset. Surveys typically don’t need to be analyzed in real time, this was just a really good example for demonstrating streaming datasets.

      Depending on the technology that you’re using, you may elect to just import the list into Power BI desktop, publish to the service, and set up a scheduled refresh.

      Sorry I don’t have better news for you.
      Jarid

      Reply
  • How can I schedule a refresh in this particular type of Data set as it is not allowing me to do so. Also, I have to refresh the data every single time when a submission is done. How it can be auto refresh?

    Reply
    • Hello Minu,

      Streaming datasets are different from regular datasets.

      There isn’t a way to ‘schedule’ the refresh, as data is pushed to these dataset via an API. Here we’re using Power Automate to push data. If you have other changes in the SharePoint list that need to be accounted for then you would need to update the trigger to your flow.

      Good Luck,
      Jarid

      Reply
  • When in the PBI action, after table selection I’m given a “payload” field. I don’t have the fields from my list/form show up as shown in your example. Please advise.

    Reply
  • hi, i realised that i am not able to transform data and edit/add columns in power BI if i were to use this method. The “Transform Data” button is greyed out. May I know how I can troubleshoot? Thanks 🙂

    Reply
    • Hi Sandy,
      Unfortunately, you cannot transform Streaming DataSets in Power BI. You can edit the data type or modify your Flows to only provide the data you want. You have two options:

        You can directly use the SharePoint List you created in this tutorial. It grants you full transformation abilities without needing to modify your flow.
        Alterenatively, there is a video version of this tutorial that uses SharePoint Lists only and requires two fewer steps to build. You can find it here.

      Hope this helps!

      Regards

      Victor

      Reply
  • When i am going through Power BI stage in Automate – it doesnt come up with the fields to add but has a field called “payload” – trying to figure out where i went wrong, but cant figure it out. Can you help?

    Reply
    • Hi Adrian,
      I’ll refer to Jarid’s earlier comment,
      The connector for the “Add rows to a dataset” has been updated by Microsoft.
      A full walkthrough of the new process can be found here.

      Hope this helps!
      Victor

      Reply
  • Hi,

    I’m not able to select ‘List of response notifications Response Id’ as response id. I only have ‘Response Id’ availabe to select. And I don’t have ‘create item’ as the next step between Get response details and Choose an action. It goes directly to ‘choose an action’. What could I be doing wrong?

    Thanks in advance for your help!

    Reply
    • Hi Tine,

      Response Id is equivalent to List of response notifications Response ID. I am assuming this is a slight difference between Form Pro and regular Forms but it could easily be a renaming caused by updates.

      When adding a new step, you are always presented with the choose an action/operation dialogue when you click on the +Next Step button.
      It remains so until you select the action or operation of choice. In this situation type “Create Item” in the search bar provided
      and select “Create Item SharePoint”.

      If you are still facing difficulties I would suggest the video version of this talk as it shows this process in action.

      Hope this helps.
      Victor

      Reply
  • Thank you so much!!

    It was really helpfull for my project! I just have a question. Do you know how can I reset the Respondent ID?

    When I was making some testing, I was sending many test answers and right now when I am implementing the project, I cannot have the ID starting in 1.

    Reply

Leave a Reply

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

Latest Posts

What is an Analytics Center of Excellence?

Chris Sorensen

Communicating with Data: A Guide for Data Analysts

Aysha Hafeez

Careers in Data Analytics

Chris Sorensen

A Primer on JSON and XML Files in Power BI

Stephen Zielke PhD

Fix the Date! String Manipulation and Date Formatting in Power BI

Victor Lajide

The Best Way to Learn Python

Tyler Long

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

Sign up for our newsletter