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

19. Linking Power BI to Microsoft Forms Responses and Showing the Most Current Submission

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

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. 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. 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. 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.

A screenshot of a Microsoft form with fields to enter zip code, rate cicada, choose a favorite sport, and tick some office 365 applications.

Figure 19-1

Sample Form

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.

A spreadsheet has 8 columns and 24 rows. Some column headers are I D, start and completion times, email, name, and used office 365 applications.

Figure 19-2

Form Responses Spreadsheet

Notice how the Which of these Office 365 applications have you used so far? responses are formatted:
Forms;Planner;Teams;Power Apps;Stream;OneDrive;Power BI;Power Automate;OneNote;

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.

A screenshot of a drop-down menu under the excel file titled sample power B I data collection.

Figure 19-3

Details in OneDrive

We then go to More details, as shown in Figure 19-4.

A screenshot of an excel sheet titled sample power B I on the top and access and activity options with more details button at the bottom.

Figure 19-4

More details in OneDrive

We now can copy the path (Figure 19-5).

A screenshot of the more details section under the sheet titled sample power B I data collection with an icon on the path link.

Figure 19-5

Copying the path in OneDrive

Here is how it looks in my case:
https://plattecanyon-my.sharepoint.com/personal/jeffrey_plattecanyon_onmicrosoft_com/
Documents/M365%20Book/Sample%20Power%20BI%20Data%20Collection.xlsx

We are now ready for Power BI .

Configuring Our Data in Power BI

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.

A screenshot of a drop-down menu under the get data tab. The web option is highlighted.

Figure 19-6

Web data source in Power BI Desktop

We then paste in the path we copied back in Figure 19-5. Figure 19-7 shows how it looks.

A screenshot of a window with the heading, from web. The basic option is selected with an entry field for the U R L.

Figure 19-7

Pasting in the OneDrive path

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.

A screenshot of the access web content window under the organizational account option. The screen has a sign-in button.

Figure 19-8

Validating M365 credentials

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.

A screenshot of a navigator window with table 1 on the right. The table has 4 columns and 24 rows.

Figure 19-9

Choosing the form responses table

We immediate Transform Data. We change the Please enter your zip code column to be Text instead of Whole Number since otherwise leading zeros are lost, as shown in Figure 19-10.

A screenshot of a drop-down menu with change type and text options selected. The screen in the background is titled please enter your zip code.

Figure 19-10

Changing zip code data type to Text

Our next challenge is with our Which of these Office 365 applications have 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 By Delimiter (Figure 19-11).

A screenshot of a drop-down window under the split column option in the tabs section. The option, by delimiter is highlighted.

Figure 19-11

Splitting the column by a delimiter

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).

A screenshot of a window titled split column by delimiter. The entry fields are for selecting any delimiter, splitting at, and advanced options.

Figure 19-12

Delimiter options to include splitting into Rows and no Quote Character

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.

A screenshot of a window titled which of these office 365 applications have you used so far. A drop-down menu with remove empty option is highlighted.

Figure 19-13

Removing empty rows

Our last transformation is to add a custom Days Since Response column (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).

A screenshot of the custom column window. On the left, a menu under the available columns has the completion time highlighted.

Figure 19-14

Days Since Response custom column

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.

A screenshot of a window summarizing response data with the file tab highlighted. An icon is on the group by option under the tab, file.

Figure 19-15

Selecting Group By on the Transform tab

Figure 19-16 shows the resulting dialog box.

A screenshot of the group by window with drop-down options like email, new column name, operation, column, max date, max, completion time, and add aggregation.

Figure 19-16

Group By Email with AllData and MaxDate columns

We pick our unique column, which is Email 4 in this case. We add an AllData column 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.

Figure 19-17 shows the result.

A screenshot has a table on the screen. A drop-down window on the screen has the expand, I D, start, and completion time options selection.

Figure 19-17

Expanding the AllData table

Notice how there are now only 14 rows (one per email address). We expand the AllData column (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.

A screenshot of add conditional column window with entry fields for the new column name, operator, value, output, and an add clause button.

Figure 19-18

IsMostRecentSubmission Conditional Column

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.

A screenshot has a map of the United States on the top and a column chart, and a pie chart at the bottom under Microsoft 365 apps option.

Figure 19-19

Visualization showing all responses

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.

A screenshot has the heading map visuals with an icon on the disabled button and a passage below it about the enabled option.

Figure 19-20

Enabling Map and Filled Visuals in the Power BI Admin portal

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.

A screenshot of a drop-down menu with the distinct count button selected under enter your zip code option.

Figure 19-21

Configuration of the Map control showing Count (Distinct)

We are now ready to limit our responses to the most recent per person. To do that, we add the isMostRecentSubmission column 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 AllData table .

A screenshot has a map of the United States on the top and a column chart, and a pie chart at the bottom with a filter section on the right.

Figure 19-22

Limiting to the most recent response per person

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.

A screenshot of the power B I visuals window has a word cloud section below all visuals tab.

Figure 19-23

Adding the Word Cloud visualization

A screenshot has 2 word clouds with a visualization section on the left. The build visuals tab has many options.

Figure 19-24

Setting up the Wordcloud visualization

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.

A screenshot of a drop-down menu for summarizing responses under all tab. 1, dataset, and 2, settings buttons are highlighted.

Figure 19-25

Going to the Settings for the dataset

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.

A screenshot of the data source credentials window has a highlighted text for failed credentials. An edit credential button is on the bottom.

Figure 19-26

Editing data source credentials

You will normally select the OAuth2 Authentication method and the Organizational privacy level, as shown in Figure 19-27.

A screenshot of configuring summarizing response window has entry fields for U R L, authentication method, and privacy level settings.

Figure 19-27

Setting the OAuth2 authentication method

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.

A screenshot of the data source credentials window has entry fields under the scheduled refresh with an apply button highlighted at the bottom.

Figure 19-28

Adding a scheduled refresh

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.

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

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