Self-paced Workshop

I wonder how many of us use shared mailboxes to funnel work? How well does a shared mailbox, on its own, work?Do all team members do their fair share of the work answering messages from the shared mailbox or does social loafing run rampant?

Maybe it is time to consider monitoring email in shared mailboxes. After all, receiving and responding to email can require a lot of effort. We all know how it is. We receive lots of email everyday and we don’t get to read all of it but we hope to get back to it. In business we may find that we probably get more email than we do in our personal lives. We get emails from vendors, wannabe vendors, co-workers, project teams, bosses, customers, etc. It seems if a person exists then we are getting email from them. So we make shared mailboxes to spread the dread of checking the mailbox among more teammates or employees and inevitably this thing becomes a work process… a work process that is hard to keep track of. Who on the team is doing what with these messages and how many things are they doing?

This is the problem that my self-paced “workshop” explores and attempts to resolve with a custom solution built using the Power Platform.

Workshop Details

This workshop will quickly review a typical shared mailbox with subfolders and then jump right in to planning and building a custom solution to help keep track of the messages in the mailbox folders and subfolders without having to manually check the mailbox. This can be a working solution for teams or managers to assist in active workflow management.

This workshop includes:

• recorded instructional material
• written instructional material

What you’ll need:

• A Power Apps license (free trial version works)
• A Power Automate license (free trial version works)
• A Power BI license (free trial version works)
• Power BI Desktop (available on Windows only)
• An active Outlook mailbox that you have access to

Step 1

Get the mailbox ready

The shared mailbox is the data source for this solution. So, it is crucial to have a well-organized mailbox.

To summarize, if you have 500 folders expect synchronization issues, performance issues and other problems. If you have more folders than that it is a miracle that you can check your email at all. If you have a delegate assigned to check your email, that person will have email problems, too. So, it is just best to avoid that nonsense altogether.

The image above depicts a normal, professional, healthy folder structure. The majority of the folders are default folders. The last four folders were created by the team or team manager for the purposes of funneling work from the Inbox by those individuals.

Task 1

Review and prepare the mailbox you plan to use for the rest of this workshop. Use the tips from Step 1 to guide you.

• Get to know your folder structure and the messages that are typically contained in each folder
• Gather ideas for what you, the team, or management routinely needs to know about the flow of work in the mailbox
• Create necessary folders and a single-level of subfolders if you need them but don’t overdo it on folder creation
• Keep the names of your folders professional
• Clean up folders for projects long past and consider moving the important bits to OneDrive or an applicable SharePoint site document collection
• Delete any folders that are empty and have not been used for a long time
• Don’t forget to delete the deleted folders from deleted items as this doesn’t happen automatically

Step 2

The Objective

In this workshop, a shared mailbox is used to facilitate business processes. There is a team of four employees that review, research, and answer questions submitted to the mailbox. The goal of this project is to quantify the general work of each employee as it relates to managing this mailbox. Here’s what our solution needs to provide management:

• The number of email messages that each employee worked on today, this week, this month and this year
• A general comparison of the amount of messages each of the four employees works on over the course of the year
• The total number of messages received by the mailbox
• The total number of messages in the Inbox

Connect your mailbox

We’ve reviewed our mailbox and cleaned out some old folders and maybe we created some new ones. We generally know the data in the mailbox and have an idea about what we routinely need to know about the mailbox.

Now, we are ready to connect our mailbox to the Power Platform. Launch Power BI Desktop and start the video, below.

https://videopress.com/v/wRPels98?posterUrl=https%3A%2F%2Feasypowerplatform.com%2Fwp-content%2Fuploads%2F2020%2F11%2Fimage-1.png&preloadContent=metadata

Add a visualization and a new table column

Before we jump into building our report, we will take a look at a single visual and how it behaves with our mailbox data. Then we make our table data work for our specific purpose by adding a column to make our visualizations more effective.

https://videopress.com/v/dTcqWT0V?posterUrl=https%3A%2F%2Feasypowerplatform.com%2Fwp-content%2Fuploads%2F2020%2F11%2Fimage-1.png&preloadContent=metadata

Task 2

Connect Power BI to your mailbox and transform the data. Use the tips from Step 2 to guide you.

• Filter out any folders you don’t need to see. This will improve the performance of your query
• Expand any columns you do need
• Create a new column needed for your visualizations
• Save your Power BI file to a location you specify (preferably a SharePoint site or some other location that is always accessible) and name it something memorable

Step 3

Build a mailbox report

We’ve connected our mailbox to Power BI, prepared our queries and saved our work and experimented with a visualization and adding a new column.

Now, we are ready to build a beautiful report from our mailbox data.

Let’s go back to Power BI and build our report!

https://videopress.com/v/xZJ0hg3G?preloadContent=metadata

Task 3

Build out your mailbox report and use the videos as a guide.

• Apply column and line charts to the report
• Apply filters to the column and line chart visuals
• Add color to the report by formatting the visuals
• Turn on/off chart options like, axis titles and borders
• Save your work

Step 4

Refine the Report

Our report is looking very good but we still need to display a few more pieces of information. We still need to display the total number of messages received (count of all messages in employee folder and the Inbox) and the number of messages currently in the Inbox. Since these are basic pieces of information, a card is a good visualization to use to present this data.

Add a Card to a Report

1. In the report, expand the Visualizations panel.
2. Click the Card visualization to add it to the report. The Card visualization is shown highlighted in the image below.
   
3. Drag the Id field to the visualization date field as highlighted in the image below.
   
4. The card will populate with the first messages unique Id which looks pretty wacky but that’s okay. Click the drop-down arrow next to the visualization data field for this card and select Count from the menu. This changes the card so that it doesn’t display the first message Id but a a count of all the message Ids. Use the image below this text as a guide.
   
5. Now edit the text in the visualization display field. Change it so that it reads Total Messages and not Count of Id.
6. Take this time to resize the card, drag it into the desired position, and edit the visualization formatting to add color and refine font attributes.
7. Save your work.

Create a second data card

Create another data card to display the number of messages in the Inbox.

1. Copy and paste the existing card onto the report to duplicate it.
2. Drag the new card and position it to the right of the first card.
3. Edit the text in the visualization display field. Change it so that it reads Messages in Inbox and not Total Messages.
4. Apply a filter to this visualization by expanding the Filters panel.
5. Use a basic filter and check the box next to Inbox to display only the numbers for the Inbox in this card. Use the image below this text as a guide.
   
6. Now the card displays a count of the messages in the Inbox, only.
7. Save your work.

Step 5

Publish the Report

Your report is ready to share with the team or management and you need to publish it to a workspace for others to view it. Just make sure users also have access to the workspace.

1. On the ribbon, click Publish. If you haven’t saved changes in a while you may be prompted to do that.
2. Select the proper workspace for the report. In the below, I should pick a workspace other than my personal workspace. Use an organization workspace associated with a Teams site or something accessible to a group. You may not want to give individual users access to your personal workspace and that information may be lost if employees leave the company.
   
3. Click Select to publish the report.
4. Once the report has published successfully, click the report link to open the report in the Power BI service. The Power BI service is the web or SaaS part of Power BI. This is where your report users and viewers access the report.
   
5. With the report open in the Power BI service, click Share as shown below.
   
6. Click Report.
7. You can share the report via e-mail. In the Share report panel, enter an email address for each employee that you want to grant report access to and add a custom message to the email. Choose the permission that the selected users will have for this report. Use the image below as a guide.
   
8. Click Share to share the report.

Step 6

Set the Report to Refresh Automatically

A report is only good as its data if that data is current. Consider keeping your report current by scheduling automatic refreshed in the Power BI service.

1. With the report open in the Power BI service, click the ellipsis on the menu and select See related content. See the image below as a guide.
   
2. In the Related content panel, click the Schedule refresh icon to launch the Datasets settings for the report . (It’s highlighted in the next image.)
   
3. Click Scheduled refresh and toggle on the Keep your data up to date option. Select the refresh frequency, your time zone and the times when the data will refresh. Choose to send refresh failure notification to yourself and if necessary, send to others. Click Apply.
   

Extra Features

By now, you have a beautiful, working solution. It easy for team members or management to check at-a-glance and the report updates on its own. What more could you want? Lots, actually.

Display the date/time the report was last refreshed

Imagine you have set your report to update every 30 minutes. Does anyone else know that? Did you tell the team but they don’t seem to remember? Consider adding information to your report to show the last time the report data was updated.

1. Open your report in Power BI Desktop.
   
   Add a New Table
2. On the ribbon’s Home tab, click Transform Data.
3. Click New Source and select Blank Query.
4. Name the query RefreshTime
5. Enter the following code in the function bar:
   
   = DateTime.AddZone(DateTime.LocalNow(),5)
   
   Press Enter.
   In the code above, the number ‘5’ is the time zone offset for my region. Enter the offset for your region.
   
6. Click the Convert to Table button on the ribbon.
7. Rename the column to RefreshDateTime.
8. Click the Transform tab and change the data type for the column to Date/Time.
9. On the Home tab, click Close & Apply.
   
   Place this information on the report
10. Add a card visualization and drag it to the top of the report.
11. Drag the RefreshDateTime field to the data field for the card.
12. Format the card so that the Category is toggled off.
13. Toggle the Title on and reads Last updated:
14. Format the Data Label so that the text size is smaller.
15. Save the report and publish the changes.
16. View the updated report. See the refresh date/time. This information will automatically updated each time the report is refreshed.
    

Show more granular data in the report

The current report shows all the emails worked or in progress for each employee but there is not a breakdown between resolved emails and pending emails. We can show this but we’ll need to add another column to our data to make this easier.

Add a conditional column

1. On the ribbon’s Home tab, click Transform data.
2. Click the Add Column tab.
3. Click Conditional Column.
4. Name the column Status.
5. Building the condition by setting Folder Path as the Column Name, contains as the Operator, Resolved as the Value and Resolved as the Output. (This works because my data has subfolders named Resolved.)
6. Enter the word Pending for the Else clause. Use the image below as a guide. Click OK to create the new Status column.
   

Use the new column in the report

1. Click the Activity This Year visual on the report to select it.
2. Drag the Status field to the Legend field in the visualization. Now the report shows the number of messages pending and resolved by employee.
   
3. Select each Activity visual and update it each by dragging the Status field to the Legend field for each of these visuals.

These are just a few the extras that you can build into your report. I hope you enjoyed this workshop and was able to create and share a monitor solution for your team.