Enhancing your SharePoint Solutions with Beautiful Power BI Dashboards

Nick Brattoli

0 comments

In this guide we briefly discuss how to create a service request process using SharePoint, Power Apps, and Power Automate to build a solution, then add visualizations with Power BI.

Enhancing your SharePoint Solutions with Beautiful Power BI Dashboards

Office 365 has had the Power Platform (Power Apps, Power Automate, and Power BI) for some time now. You’ve probably migrated to the cloud and started using SharePoint, but you can do so much more by adding Power Apps and Automate into the mix.

Once you’ve gotten to that point, your solution can go from good to great with clever application of Power BI visualizations.

In this guide (which is a companion to the session with the same name from the Excel and Power BI conference), we will briefly discuss how to create a service request process using SharePoint, Power Apps, and Power Automate to build a solution, then add visualizations with Power BI. If this sounds interesting to you, keep reading!

Process Overview

Every organization has one or more processes that can be improved. In this example, we are going to use a service request process to demonstrate possible solutions.

Old Non-Automated Process

The original service request process was probably handled either face-to-face, over the phone, or via email. This worked sometimes, but as you would guess, having multiple sources with no formal record or conversations or transactions is an easy way to lose information.

The previous service request process followed a path similar to this:

  • A request is given face-to-face, via email, or phone
  • The call is not answered, the email is lost, or the person isn’t at their desk
  • After multiple attempts, the request may arrive at the person who needs it, but with incomplete information or no record of the request can be found
  • The request may or may not be finished

The result was a customer who does not get the help they need.

New Automated and Controlled Process

With the new process, everything is automated and tracked, and the flow of individual steps is illustrated in the graphic below.

The new process can be summarized as follows:

  • Internal party/user requests service (PowerApp)
  • Outside parties could email if necessary (shared mailbox flow)
  • The dispatcher gets notice (optionally schedules) and approves
  • Service person gets notice (or planner task or todo)
  • Service person performs service
  • Update ticket or planner (which flows to SharePoint)
  • Analytics show up on Power BI dashboard
  • The dashboard is embedded on a page in SharePoint

How can you accomplish this?

Design your Data

When trying to automate a process, you initially need to assess the entire business use to get a clear understanding of how and why then want to use it. Next, you need to look behind the scenes and consider the data storage and any limitations that may be in place. Talk to the stakeholders on your project and find the answers to the below information:

  • What information are you collecting directly?
  • Who is doing the data input?
  • Where does the data originate?
  • Where will it be stored (we recommend SharePoint)?
  • How fast do you need updates?
  • Is there any information that can be automated or validated?
  • What information are you trying to learn/measure?
  • What insights do you need in order to make decisions?

After you figure out the answers to those questions, you can design your data structure. It helps to list all the fields on your Power Apps form and their types so you can build them in the data storage location (SharePoint in this case).

For example:

Field name: Title

Field type: text

-----------

Field name: Division

Field type: Choice (division 1, division 2, division 3)

Knowing what your data will look like before you build anything is very valuable

Create a portal

Once you have your data planned out, it’s time to create your SharePoint site and give a place for your data to be stored.

  • Create a dispatcher page to hold the Dispatcher App
  • Request page for Requests App
  • Technician page for Technician App

SharePoint List

  • Create your SharePoint list with the content types you outlined during the planning process
  • Add column forming if you want to have color-coding on your choice fields

Build the Power Apps (Create your entry points)

Use Power Apps to create three separate Power Apps for the three use cases.

Create a Request App. This allows new service requests to be raised.

Create an Admin App: This allows the service requests to be allocated or assigned to the relevant technician.

Create a Field Technician App: This allows the individual technician to review the services requests that have been allocated to them and log the amount of time they spend on each callout before finally marking it as complete.

Automate your process with a flow: Once you have your Power Apps connected to your SharePoint list, you can build a flow to send notifications and change statuses on your requests.

The key stages of this flow are as follows:

  • When an item is created, notify dispatch
  • Dispatch approves on the dispatch app and assigns a technician
  • Email to the service technician, which they accept
  • Service person completes from technician Power App
  • The request is updated with completion time, status, and notes
  • A notice goes out to requester and dispatcher when complete

Building your Power BI Solution

Now that you have a complete solution with data saved into a flat SharePoint table, you may find that you want to take this to the next level by creating some useful reports and analysis to make sense of the raw service request data. It’s time to perform advanced analytics with Power BI.

Consider Licensing

First, you need to consider which licenses you need to create and view your visualizations

  • Figure out where will it be published
  • If you are just going to use a local copy and share from the desktop app, you can use E3 licenses
  • When embedding in SharePoint and publish to the web, you need E5/Power BI premium licenses for you and all viewers

Create a Workspace

After deciding to store your reports on the web, you need to create a workspace in Power BI. A workspace is where you will store your dashboards, reports, and users who have permissions to them (this creates an Office 365 Group).

Give your new workspace a name, description, and you can upload an image if wished.

Use the Power BI Desktop App

Now that you have your workspace, it’s time to start creating your reports. We are going to do that with the Power BI desktop app, as this allows us to access our SharePoint list and data. Using the desktop app is better than using the Power BI web interface because it has more capabilities.

Get your Data

First, you must connect to your data. You can connect to many different data sources. In this situation, we’re connecting to SharePoint.

Tip: It’s generally much easier to perform any necessary data calculations, transformations, validations, adjustments or concatenations etc. in the initial SharePoint list. Performing the same calculations in the Power BI model can lead to performance and lagging issues.

When getting the data, we noticed that there are a lot more fields in the SharePoint list than we need!

You can fix this with the “transform data” button:

On this screen, you can remove columns, and even add data directly with the “enter data” button.

  • Use the “remove columns” button to remove fields
  • Undo changes with the “applied steps” panel
  • Enter additional data with the “enter data” button
  • Once you are happy with the data, select “Close and Apply” to save it

Other Data Sources

There are many other data sources you can connect to, like SQL and even regular web pages that have tables on them (like Wikipedia)

Using the same “get data” button, you can enter a web URL (for example https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes

You can also get data from API sources, though they do not refresh very fast.

Refreshing your Data

Speaking of refreshing, most data sources do not instantly refresh in Power BI. By default, you would have to schedule refreshes (up to 6 times a day) with SharePoint lists. If you’re in a larger company, you may be able to get unlimited refreshes.

Other data sources like Azure SQL do offer instant refresh, and some sources like APIs have to be manually refreshed.

Creating your Visualizations

After you connect to your data, you can start creating visualizations.

Use the panel on the right to select your visualizations and place them on your report pages.

  • Each visualization has different settings
  • Click and drag fields from your data sources into the visualization’s properties to make changes
  • You can add color, borders, headings, etc. to each visualization
  • We use multiple “matrix” visualizations in this demo to sort and display data, similar to a pivot table
  • Visualizations that share a page in a report all filter on each other using what is called “focus mode”

Filtering your Visualizations

You can filter your visualizations, pages, or entire report using the filtering settings.

You can also use slicer visualizations to make pre-configured filters for your visualizations.

Our Visualizations

Using mostly matrix visualizations (and one Map), we are able to see all this data at a glance:

  • In-progress service calls
  • Average service time (last 30 days)
  • Total service calls last 30 days vs completed
  • Value is insights into staff management, scheduling, routes, etc.
  • Country codes (we actually included this on page 2 of our solution)

Publishing your Report

So now that we have our reports, how do we display them? The first way is to just save your report and send the file it creates to anyone who wants to view it in the Power BI desktop app.

Power BI Web

We want to show our information online, so we are going to use the Publish button to send the report to the workspace we created earlier:

Once your report publishes successfully, you can view it on Power BI

Dashboards

  • You can also create dashboards in your workspaces
  • A dashboard is used to pin individual visualizations from different reports onto one central place
  • Dashboards are easy to view on mobile devices, so are good for individuals in the field

SharePoint

While you can look at your reports directly in Power BI, we want to show them in SharePoint. We do this by getting an embed link from Power BI by clicking file -> embed in SharePoint Online

After that, go to your SharePoint site, create a new page, and add the Power BI web part:

Paste the embed link you got from the previous step:

And now your report is in SharePoint!

Conclusion

It’s always smart to automate your processes to keep people accountable and your data safe. Use Power Apps and Power Automate with SharePoint to facilitate your requests. Once you have your solution in place, you can use Power BI to create visualizations and make predictions based on your data. For more information, watch the companion video to this guide, and good luck with your solutions!

0000-00-00 00:00:00


Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}