© 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_4

4. Creating a Survey Response Dashboard with Power BI

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

Power BI is a powerful tool for visualizing data. Of particular relevance for this book is its ability to use SharePoint lists as a data source. In this chapter, we will demonstrate how to use Power BI to display SharePoint survey data. This is particularly useful for a long-running survey such as for a help desk. The ability of Power BI to slice the data and separate out more recent submissions is crucial to spotting trends and addressing any issues.

SharePoint's Default Visualization

As shown in Figure 4-1, SharePoint’s built-in graphical summary displays all results. If the survey has been in place for the past five years, seeing recent trends are impossible. In addition, there is no way to click on the Very Dissatisfied responses, for example, and drill down into their comments or other information.

We will use the free Power BI Desktop application to build these visualizations. Those with the appropriate licenses can then publish the visualizations to the Power BI Service, which is much more convenient as viewing can be in the browser directly, via Teams, via SharePoint, or even within Power Apps. Another advantage of the Power BI service is the scheduled refreshes of the data. Still another is the fact that the viewer of the report doesn’t have to have their own credentials to the data source, in this case the SharePoint list.

A Summary report of satisfaction survey shows the parameter options from very dissatisfied to very satisfied for 2 questions.

Figure 4-1

SharePoint’s graphical survey summary

Connecting to Our SharePoint Results

To get started, we select Get Data in Power BI Desktop and choose SharePoint List or SharePoint Online List , as shown in Figure 4-2. You normally want to select the newer (2.0) implementation. This limits the need to rename columns that have been truncated, for example. However, for survey data, you want the 1.0 version so you get important information such as the date for the response.

A screenshot for the context menu of get data drop-down menu shows all share point online lists. A box contains site U R L along with the implementation radio buttons activated for 1.0.

Figure 4-2

Getting Data from a SharePoint List

We put in the path to our site (not including the survey itself). Power BI will prompt us to either use our current credentials or to log into our Microsoft account, as appropriate. It then allows us to select one or more lists , as shown in Figure 4-3.

A table for the Satisfaction survey with 4 columns and 12 rows with load, transform data and cancel buttons at the bottom. A list of display options are shown at the left

Figure 4-3

Selecting a SharePoint Survey List in Power BI Desktop

Our next step is to Transform data , as shown in Figure 4-4.

A demonstration of available options under the transform data drop-down menu. The option is found under the table tools of the top menu bar.

Figure 4-4

Transform Data in Power BI Desktop

Power BI gives us a nice interface to filter our data, rename columns, and much more. It shows all the actions in APPLIED STEPS so we can view, edit, or remove them later (see Figure 4-6). Better yet, these steps are re-accomplished on the live data each time we refresh the data. You might notice in Figure 4-4 that the column names are truncated as in How satisfied are_x0. It will be most convenient to rename them to the full text of the associated question. To do that, we right-click on the column and select Rename. We then copy in the full question from SharePoint.

Some of the columns are displayed as Record or Table . In that case, we click the button on the right side of the column, as shown in Figure 4-5. We choose to include the Title column, which has the first name and last name. We then rename it to Submitter.

A drop-down menu under the author shows a list of columns. It points to the title with a check mark alongside. The ok and cancel buttons are there at the bottom.

Figure 4-5

Expanding a Record column into individual elements

To display responses that are within a specific range, it will be useful to create a column that shows the number of days between today’s date and the data of the response. To do this, we go to the Add Column tab and then select Custom Column . We will name the column DaysPassed and use this formula:
DateTime.From(DateTime.LocalNow()) - [Modified] + #duration(0, 6, 0, 0)
This gives the number of days since the survey was modified. We add six hours since, in my case, the Office 365 server is six hours later than my local time. Since we don’t care about decimals, we Change Type of the column to a whole number. Figure 4-6 shows the results.

A table presents a list of submitter, editor, and days passed. On the right, it has the property for the name and a list of applied steps.

Figure 4-6

Expanded and custom columns in Power BI Query Editor

Visualizing Our Data

Now that we have the data like we want it, we are ready to make some visualizations. We go to the Report view and create a Pie Chart visualization . We drag the question into Details and then the Id column as the Values, as shown in Figure 4-7. Power BI defaults to the count of the ID, which we then rename as # of Responses. This then shows us the number of responses of each type (Satisfied, Dissatisfied, etc.).

A pie chart depicts the percentage of dissatisfied, neutral, satisfied, very satisfied, and dissatisfied reviews. On the right, the fields for satisfaction point to the details and I d to the values for the number of responses.

Figure 4-7

Pie Chart Visualization in Power BI

Rather than letting Power BI define the colors, we next go to Format – Visuals – Slices – Colors to give our own colors for each of the response types.1 Figure 4-8 shows the results. We use a Slicer and connect it to the Created date2 (which we rename Survey Date). We use the Table visualization to display detailed information such as the name of the person who filled out the survey.

Four pie charts show the data for satisfied, dissatisfied, neutral, very satisfied and dissatisfied reviews in the survey. A table at the bottom right presents a list of comments along with the submitter and the date.

Figure 4-8

Completed visualizations showing pie chart per question, slicer of the survey date, and table of additional information

One of the most powerful features of Power BI is the ability to drill down into specific data. In Figure 4-9, we see the results of clicking on the Dissatisfied result in the first question. The other questions adjust to how they were answered for the survey(s) that were dissatisfied for the first question. The table adjusts as well. The manager can then contact the customer to get more details and ensure the problem does not happen again.

Four pie charts show the number of responses on the scale of satisfied, neutral to dissatisfied. The cursor hovers over the dissatisfied data of one chart showing the percentage of response as 16.67.

Figure 4-9

Drilling down on a Dissatisfied result in Power BI

If you have Power BI Pro, you can publish to the Power BI Service via Power BI Desktop. It then displays in the browser, as shown in Figure 4-10. This has the huge advantage of not requiring the installation of Power BI Desktop on each client machine.

Four pie charts for various survey questions show the data for a range of time from June 2018 to May 2022. It has a list of options on the navigation pane at the left.

Figure 4-10

Published Power BI Report

You can then embed the report into a Teams channel (Figure 4-11) and/or SharePoint page (Figure 4-12).

A screenshot indicates adding a tab in Power B I. Under the workspaces tab is a list of names from which the SharePoint survey is highlighted.

Figure 4-11

Adding Power BI as a Tab in a Teams Channel

A list of icons appears under the data analysis and regional information menus. The mouse cursor highlights Power B I under data analysis.

Figure 4-12

Adding Power BI to a SharePoint Page

Summary

In this chapter, we used SharePoint survey responses as our data source within Power BI. We transformed our data and added a Days Passed custom column that allowed us to easily create filters to show the most current week, month, quarter, and year if desired. We also saw how to customize the colors of our data to match up with our expectations that green is good, red is bad, and yellow is in-between. Finally, we learned how to display our reports in Teams or SharePoint.

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

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