This chapter stems from some real-world examples where we wanted to use Microsoft Forms but needed a mechanism for users to submit updated information, either because they changed their mind or had new data. We will examine how to address that in Power BI so we only see the most recent submission. We will also look at how to address multiple answers in our form and incorporate a map control.
How to Decide between Forms and Power Apps
When I help organizations design a Microsoft 365 solution to meet their data entry and storage requirements, here are the main things I look at:
1.
Do users need to edit their data later? If so, that argues toward a Power Apps or SharePoint front end. If not, Forms
becomes a strong candidate.
2.
Is it appropriate for users to see ALL of the data? If so, the easiest thing is the built-in SharePoint
form that goes with a list. Next is the standard Power Apps template form that mirrors the SharePoint list. If not, then either Forms (no editing) or Power Apps
(editing) would be optimal. The Help Ticketing system
we looked at earlier is a good example. Users only needed to enter a few items to open the ticket. The rest of the data (category, notes, assigned technician, etc.) was in the SharePoint list, but only editable in the Technician’s application. If there is only limited information such as status and notes that needs to be edited (and there are only a few people with editing rights), I will often just stick with the standard SharePoint form, with the column order edited to put the data to be changed at the top of the form.
3.
As much as I love Power Apps, Forms
is much easier. This is particularly true for non-technical users. So if Forms can meet the needs, I go with it.
A permutation of the preceding #1 is the situation where a user doesn’t need to edit their data but perhaps needs the opportunity to submit a new version. If we can use Power BI to only show the most recent submission, we have a whole category of applications that can be accomplished in Forms instead of Power Apps. Plus, we get a good version history of a user’s submissions.
Sample Microsoft Form
Figure 19-1 shows our sample form
. We ask for users’ zip code
, what they think of cicada’s,1 their favorite sport (including an Other choice), and what Office 365 applications
they have used so far.
In real life, I made the form anonymous, but for our purposes, I put in musical users so that we can get the most recent response per user. The results spreadsheet is shown in Figure 19-2.
Notice how the Which of theseOffice 365 applicationshave you used so far? responses are formatted:
We will have to deal with this once we get to Power BI.
Getting the Path in OneDrive
I would normally use Power Automate
to copy each response to a SharePoint list and then connect to it instead, but that’s not necessary for this example and allows me to cover how to connect to a file in OneDrive
2 as well. Our first challenge is to get the path. Figure 19-3 shows how we get started by clicking the … next to the file and then select Details.
We then go to More details, as shown in Figure 19-4.
Also, you might reasonably expect that we use the Excel workbook data source, I’ve had better luck with the Web source, as shown in Figure 19-6.
We then paste in the path we copied back in Figure 19-5. Figure 19-7 shows how it looks.
If we haven’t logged into Microsoft 365
recently in Power BI Desktop, we need to switch to Organizational account and sign in, as shown in Figure 19-8.
We take advantage of the fact that Microsoft Forms
saves its response data as an Excel table and thus choose Table1, as shown in Figure 19-9.
We immediate Transform Data. We change the Please enter yourzip code column to be Text instead of Whole Number since otherwise leading zeros are lost, as shown in Figure 19-10.
Our next challenge is with our Which of theseOffice 365 applicationshave you used so far? column. As we discussed before, this type of data is a challenge and needs to be split out so that we are not counting combinations of responses rather than the responses themselves. One interesting difference between using Power Automate to copy Forms results to SharePoint is that SharePoint puts the data in JSON format like this:
["Planner","Forms","Teams","Stream"]
Leaving the data in Excel makes it values delimited by a semicolon:
Planner;Forms;Teams;Stream;
To handle this, we Split Column and choose ByDelimiter (Figure 19-11).
Power BI
detects our semicolon. We tell it to use each occurrence and go into Advanced Options to split into rows3 and not have a quote character (Figure 19-12).
We are almost there, but you may have noticed that there is a semicolon at the end of the data as well. That gives us a blank row for each response. Luckily, Power BI has a Remove Empty option. The blank rows and selecting Remove Empty are shown in Figure 19-13.
Our last transformation is to add a custom Days Since Responsecolumn
(Figure 19-14). This allows us to easily show “responses in the last 30 days” and similar time frames. This is also a major advantage of using Power BI over the built-in response summary in Forms (which visualizes all data).
We change the data type to Whole Number and are now ready to tackle limiting the data to just to the most recent submission for each user. For that, we go to Transform tab and choose Group By, as shown in Figure 19-15.
We pick our unique column, which is Email4 in this case. We add an AllDatacolumn
with the corresponding AllRows operation. That will put all of our existing data into a new column. We add one more column, MaxDate, which we define as the latest (Max) of the Completion time column. Very importantly, then, MaxDate will hold the latest completion time of all the rows with that particular email address.
Notice how there are now only 14 rows (one per email address). We expand the AllDatacolumn
(each row of which contains a Table) and choose all our previous columns except Email (since we already grouped on that).
Our last step is to create an isMostRecentSubmission conditional column
, as shown in Figure 19-18.
This column has True if that row’s Completion time matches the MaxDate (and thus is the most recent submission) and False otherwise.
That’s it. We select Close and Apply and then move onto our visualizations.
Visualizing Our Data in Power BI
We create pages/tabs for Cicadas, Favorite Sport, Microsoft 365 Apps, and Word Cloud
, as shown in Figure 19-19. We are initially showing all responses but will limit to the most recent shortly.
We use a Card to show the number of responses. It is critical to use Count (Distinct) since we split the 365 apps into multiple rows back in Figures 19-12 and 19-13. We have slicers for Days Since Response and zip code
. We show the number of responses per product in a bar chart, treemap, and pie chart. Our zip code data allows us to add the Map control
. If you get a warning message and want to use that control, a Power BI Admin will need to enable Map and filled map visuals, as shown in Figure 19-20.
From there, Figure 19-21 shows how we put the zip code
in the Location field and use the Count (Distinct) [# of Responses] as the Bubble size.
We are now ready to limit our responses to the most recent per person. To do that, we add the isMostRecentSubmissioncolumn
to Filters on all pages. We select only True, as shown in Figure 19-22. Note how the number of rows matches our number in Figure 19-17 before we expanded the AllDatatable
.
The 39 True values shown correspond to all the split-out rows for Office 365 products but there are only 14 unique responses.
Since the favorite sport and product names are conducive to a word cloud, we choose to add more visuals and search for the free Word Cloud control
, as shown in Figure 19-23.
Figure 19-23 shows how we configure it with the Category (Favorite Sport or Office Product) and Values (# of Responses).
Notice how each word gets visualized separate, so Power and BI show up as two different words.
Setting Up a Scheduled Refresh
To complete the job, we need to publish our visualization to the Power BI Service and then configure a scheduled refresh
. That will allow changes to our OneDrive
spreadsheet to automatically be reflected in our visualization. To get started, we go to Data hub on the left side of the Power BI screen, find out dataset, and then go to Settings. This is shown in Figure 19-25.
You will normally have to configure your Data source credentials, as shown in Figure 19-26, before you can schedule a refresh. Until you do, that option will be disabled.
You will normally select the OAuth2 Authentication method
and the Organizational privacy level, as shown in Figure 19-27.
This will then prompt you for your normal Microsoft 365 email/credentials.
At this point, you are able to configure one or more scheduled refreshes
, as shown in Figure 19-28.
You can set the frequency, time zone, and time of day. It is a good idea to add additional contacts for refresh failures if this is an important visualization.
Summary
In this chapter, we learned how to connect to a OneDrive Excel file
and split our multiple responses by their delimiter
into multiple rows, removing the resulting empty rows caused by the ; at the end of each set of multiple responses. We explored using the Group By feature to only use the most recent submission from each user. We saw how to use the Map control
with our zip code data
and enable it if needed in our environment. Another key point was authenticating our data source after publishing to the Power BI service, which then allowed us to schedule automatic refreshes of our data. In our next chapter, we will examine another key challenge of working with Microsoft Forms data – how to get our hands on their attachments and copy them to SharePoint.