© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2022
J. M. RhodesCreating Business Applications with Microsoft 365https://doi.org/10.1007/978-1-4842-8823-8_5

5. Creating a Survey Solution with Forms, Power Automate, SharePoint, and Power BI

Jeffrey M. Rhodes1  
(1)
Colorado Springs, CO, USA
 

An exciting feature of Microsoft 365 is Microsoft Forms , which makes it very easy to create forms and surveys. In this example, we will show create a survey for our help desk in Forms (similar to what we did with a built-in SharePoint survey in the last chapter). Since Forms tend to wait until requested to create an Excel spreadsheet of the responses, I like to use Power Automate to copy each survey response to a SharePoint1 list. We then use this SharePoint list as our Power BI data source.

Configuring the Form

One big advantage of Forms over SharePoint’s built-in survey capabilities is the ability to allow anyone to respond. This is configured on the Share tab, as shown in Figure 5-1.

A screenshot of a drop-down menu. Collect responses is selected and the cursor is on anyone can respond.

Figure 5-1

Sharing a form to allow anyone to respond

Figure 5-2 shows a section of a survey .

A screenshot of a survey questionnaire. It features 2 questions with rating option, a comment box with text excellent job, and a submit button at the bottom.

Figure 5-2

Microsoft Forms Survey

Figure 5-3 shows the resulting form in SharePoint.

A screenshot of a rectangular block with 4 options from left to right: choice, text, rating, and date. The cursor is placed over the rating option.

Figure 5-3

Microsoft Forms question type: Choice, Text, Rating, and Date

Notice that there is a built-in Rating question type where you can set the number of levels (the preceding has five). Figure 5-3 shows the interface when you click the Add new button. You can also choose to include a subtitle, make the question required, allow a “long answer” (for the Text question type), limit to a number or range (again for Text), allow multiple answers, the “shuffling” of answers, and/or an Other option (Choice type). Since we will automatically get the date when we add the response to SharePoint, I don’t normally include a Date type unless I am looking for them to enter a date in the future such as the “required by” date.

Adding the SharePoint List

Our next step is to create a list (Figure 5-4) in SharePoint for holding the data. I typically make all the columns Single line of text or Multiple lines of text. 2 This avoids data type problems when being transferred from Power Automate. We can fix the data types once we get to Power BI.

A screenshot of a table with 10 columns and 4 rows. It lists 4 issues, their status of resolution, and other pertinent data.

Figure 5-4

SharePoint list for holding the Forms survey data

Creating the Power Automate Flow

Within Power Automate, we create a new flow from a template and search for SharePoint, as shown in Figure 5-5. We choose the one to Record form responses in SharePoint.

A screenshot of 2 adjacent tabs. The cursor points to the template option under start from a template on top and to the record form responses in SharePoint at the bottom.

Figure 5-5

Creating a flow from a SharePoint/Forms template to record responses

From there, we pick the form and fill in each field in turn (Figure 5-6). Notice that the template includes the Apply to each action. If you were building this flow from scratch, you would need to add this action. We get the List of Responses in each case.

A screenshot of 2 tabs. The top one titled when a new response is submitted is pointing to the bottom one titled apply to each.

Figure 5-6

Filling out the Power Automate template

We then select the SharePoint site and list that we want to populate. From there, we go to each list column and select the corresponding form entry. The beauty of Power Automate is that it presents you with the relevant information you need. In this case, it is the list of questions from the form that need answers to be copied to SharePoint. All this is shown in Figure 5-7. Note that the form questions are labeled by the text of the question. As many of these start with How satisfied were you …, it is helpful to have the form open when doing this step.

A screenshot of 2 adjacent tabs. The file titled how satisfied were you with the technical skills under dynamic content points to the empty field for technical skill under create item tab.

Figure 5-7

Matching Form answers to SharePoint columns

Once you save the flow, fill out the form and then go to both Power Automate and SharePoint to see the results. If there are errors, click on the Run history to see the details.

Visualize in Power BI

From here, the task is very similar to the previous chapter, where we connected Power BI to a SharePoint survey. We rename the columns as needed and update the data types. In particular, we update the Survey Date column to be DateTime and the various rating columns to be Whole Numbers. We create a DaysPassed column with this formula :
=DateTime.From(DateTime.LocalNow()) - [Created]
This allows us to segregate responses into the most recent week, month, quarter, etc. as needed. This type of data also lends itself to Measures as well. I like to distinguish between custom columns and measures this way: custom columns are good for manipulation of data that depends on a single row while measures are good for data across rows. Our DaysPassed column from earlier depends on the single value of the Created column (together with figuring out today’s date) and thus should be a custom column. But if we want the average over multiple rows, that will be a good measure. Figure 5-8 shows how we add a measure. We select New measure, enter the name (Average Courtesy in this case), and then the formula using the Data Analysis Expressions (DAX) language. In this case, we use the AVERAGE function and then select our [Technician Courteous] column.

A cropped screenshot of a tab in Power B I. 1, 2, and 3 are marked on the new measurement button, value for average courtesy, and survey response technical courteous, respectively.

Figure 5-8

Adding a Power BI Measure

Figure 5-9 shows how the measure shows up with other Fields on the far right and how to use it just like another data element. In this case, we show it in a card visualization. The nice thing is that the measure takes its values from the current context. So if the user slices or drills down on the data (by editing the Survey Date slicer or drilling down on Technical Skill, for example), the measure values automatically update.

A screenshot of the visualization tab has pie charts for responses by courteous and of responses by technical skill. The cursor points to the ticked checkbox for average courtesy under fields.

Figure 5-9

Configuring the Average Courtesy Measure

A screenshot of the visualization chart has a table with columns for survey date, courteous, technical skill, timeliness, overall service, and submitter. Pie charts on the left summarize 4 responses.

Figure 5-10

Power BI showing survey results with pie chart visualization, slicers, and measures

We also update the Data colors again so that low score (1) is red, a high score (5) is green, and the ones in between transition from orange to yellow to light green.

Summary

This chapter introduces what I find to be a very powerful use case: collecting data via Microsoft Forms, using Power Automate to copy it to SharePoint, and then visualizing it via Power BI. This protects the data and allows improved drill-down and slicing of data by response date and other metrics.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.48.3