October Sale - 20% of all courses - Book Today

To Review

In Episode 1, we went through the creation of a short survey using the Microsoft Office 365 App called Forms accessible at forms.office.com. Remember there are various options when creating your form such as:

  • The type of questions (Text, rating, dates, etc)
  • branching options to create a flow to the next question based on the user’s previous answer
  • using the Settings panel to control who can access your Form and when it’s open
  • applying a theme to your form to help engage your respondents

 

This week we are going to look at how we can work with the data our Form collects when our survey respondents fill it in.

There are several ways we can do this:

  • Simply download an Excel file from your Forms Responses page
  • Use the Microsoft Office 365 App Flow to add each Form response to either a SharePoint Site, or a Power BI streaming dataset

 

Downloading an Excel File from Forms

This is the easiest way to access your Form data and analyse the responses you have gathered.

Simply click on the Responses Tab of your Form and click on the Open in Excel link:

Click Open in Excel



This will download a static version of the Form data (it’s not linked to the Form and won’t update). You can then analyse the data in Excel, the data is already in an Excel Table so creating Pivot Table and Pivot Charts is a great way to see what your users are saying.



 

Using Flow to add Form Responses to SharePoint Lists

This is a great use of the Microsoft Office 365 workflow automation App Flow. When someone responds to our Survey that will Trigger a flow of actions.

First we need to go to Flow at flow.microsoft.com.

In Flow there are many Templates that you can use and modify for your purpose, if required. On the left-hand menu select Templates and then search for Forms. Many different templates appear with various actions triggered by a Form response.



Click on the first tile (or the tile labelled Record form responses in SharePoint), this will first confirm any required connections (in this case Forms and SharePoint) before loading the Flow into edit mode for you to setup.

 

The Flow is relatively easy to setup, much of it will pre-populate based upon your user account and connections. First we need to select our Form that we want to connect to:

While we have setup our Trigger (when a new response is submitted to our Student Survey), we also need to tell Flow which Form item we want to get details from. In the next step Get Response Details also select the same Form Name:



 

We then want to create a List in our SharePoint Online Site to store our responses.

The List should have a column for each piece of data you want to capture including respondents email, response time and a column for each question (remember to use the right data types for your columns – numbers, text, dates, etc)



 

In your Flow you can then choose your SharePoint Site name and the List you created. Remember only the sites and lists you have permission to access and edit will be visible. Some lists won’t show up automatically, but you can edit a “Custom Value” with the List name (check your spelling as well!)

Once this is selected the list column names will populate. Select each column name and the Dynamic Content Menu will appear on the right. Here we select Items like the Survey Questions and assign them to columns in our SharePoint List. When the Flow runs, it will add those values to our list:



Then Click Save and you are finished! When someone responds to the Survey, their responses will now be entered on the SharePoint List.

 

Adding the Forms data to a Streaming Dataset

We will continue to use the same Flow as before and add an additional action. We want to also add the Forms data into a streaming (live) dataset, so we can see instant analysis in a Power BI report or dashboard.

First add a Parallel Branch to the Flow:



Then add the Power BI action “Add rows to a dataset (preview)” – notice that this action is still in preview mode so it’s possible you may encounter issues or bugs until it’s made generally available.



Next we need to create a streaming dataset in our Power BI workspace. Browse to app.powerbi.com and locate the workspace you want to create the dataset in.

Then click on the name of the workspace to see it’s contents. In the top right-hand side select New and then Streaming dataset:

 



Next select the API streaming dataset and click Next:



Just like our SharePoint List we also need to create the values in our streaming dataset for each of our Form responses (remember to select the correct data type for our responses). Script will be created automatically for you (you can ignore this for now). At the bottom turn Historic Data Analysis ON, this will store the live survey responses (and it will store about 5 million rows of data!) and click Create:



 

Now we can return to Flow, and in our new Power BI action we can select our Workspace, Dataset, Table and input our Survey Values:



Our Flow now will save the responses in SharePoint and into a streaming dataset. We might not want to do both of these. But it gives us a record in SharePoint and a live analysis of results in Power BI. You can always just use Flow to do one or the other. Or you could use Flow to add Form responses to an Excel Spreadsheet stored on OneDrive – many possibilities!!! If you like what you see then check out our Office 365 courses.

In Episode 3, we will look at how we can create a Power BI Report. Based on these datasets and embed the Report into a SharePoint Online page.

Contact Us

What is Remote Training?

Valued by Individuals

4.8/5
Over 23623 Reviews
Very good course - I learnt a lot. Tamara was really patient and helpful. -R Intermediate Brisbane
Elly - R Intermediate, .
Learnt lots good stuff to use for my work. Thank you -Excel Beginner Brisbane
Adam - Excel Beginner, .
Thank you for your time and it was very informative. Many Thanks -Excel Beginner Brisbane
Morgan - Excel Beginner, .
it covered all my needs. Thanks -Excel Beginner Brisbane
Rohan - Excel Beginner, .
Great learning and enjoyed . -Excel Beginner Brisbane
John - Excel Beginner, .
Very pleasant, informative day. -Power BI Beginner Brisbane
Bobbette - Power BI Beginner, .
This has been a very helpful introduction to Power BI - we'll be able to move some of our reports across to Power BI sooner than we expected. -Power BI Beginner Brisbane
Karen - Power BI Beginner, .
Great beginner session - trainer very personable. -Power BI Beginner Brisbane
Tracey - Power BI Beginner, .
Very helpful and hands on. A comprehensive look at the basics of PowerBI - definitely interested in learning more in the intermediate course. -Power BI Beginner Brisbane
Daniel - Power BI Beginner, .
Very valuable session. Can definitely see how we will use this in the workplace. Steven was a great trainer and kept the room going all day, -Power BI Beginner Brisbane
Amanda - Power BI Beginner, .
Steven was a great trainer. -Power BI Beginner Brisbane
Fay - Power BI Beginner, .
Great overview. -Power BI Beginner Brisbane
Barbara - Power BI Beginner, .
Very informative, the presenter was thorough and welcoming to all. I never felt uncomfortable asking questions. I am also grateful for the Course Manual, so I can review the Agenda topics again at my own pace. -Excel Intermediate Sydney
Janelle - Excel Intermediate, .
excellent teacher very impressed -Excel Intermediate Remote Online
Michelle - Excel Intermediate, .
Very informative. Some great hacks -Excel Intermediate Remote Online
Matthew - Excel Intermediate, .
Thanks Michael, you were patient and happy to go back over knowledge whenever people needed it. I love how excited you got over some of the technical aspects - they were really interesting! Sometimes it was a little hard to hear, might have been connectivity issues on my end, not sure. Thanks so much! -Excel Intermediate Sydney
Kristie - Excel Intermediate, .
The topics chosen were very useful. I especially think the If statements, vlook up, pivot table and flash fill will work well for me in the future. Michael was very helpful and always stopped to assist those who needed further info. He was also happy to answers questions in general related to excel. Potentially needed some polls or questions within the training to check learning and understanding. -Excel Intermediate Sydney
Jessica - Excel Intermediate, .
Michael is very good teacher. It was a great session and learned a lot in a short time. -Excel Intermediate Sydney
Philip - Excel Intermediate, .
very good. Need to practice now -Excel Intermediate Sydney
David - Excel Intermediate, .
Jason gave us a really good overview of both Power BI service and desktop and was very helpful answering our questions. The course was very helpful in cementing and extending my basic understanding of Power BI -Power BI Beginner Canberra
Paul - Power BI Beginner, .

Trusted by Business

south east asia microsoft training courses nexacu

Follow us