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.
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.
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.
Our next step is to Transform data, as shown in Figure 4-4.
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.
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:
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.
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 Chartvisualization
. 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.).
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 Tablevisualization
to display detailed information such as the name of the person who filled out the survey.
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.
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.
You can then embed the report into a Teams channel
(Figure 4-11) and/or SharePoint page
(Figure 4-12).
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.