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.
Figure 5-3 shows the resulting form in SharePoint.
Notice that there is a built-in Rating question type where you can set the number of levels (the preceding has five). Figure 5-3shows
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.
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.
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.
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.
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.
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.
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.