PowerBI: Visualize your data in dashboard in 10 minutes

Introduction: In this post I will showcase how to create a Power BI dashboard in 10 minutes that connects to a SharePoint list as the data source. In the previous post, PowerApps: Be a hero and transform business process, in 10 minutes (and without writing code!), I walked you through how to create a PowerApp and use it to enter data in a SharePoint list. In this post, we will take that data and present it in a Power BI dashboard to make it easier to visualize the data.

First, Download Power BI Desktop from www.powerbi.com and install it on your PC. This is required in order to connect to SharePoint Online lists. Once downloaded, launch Power BI desktop and select Get Data from the toolbar:

In the Get Data dialog box, click Online Services on the left, then on the right highlight SharePoint Online List and click Connect:

In the SharePoint Lists dialog box, type the URL of the SharePoint parent site and click OK:

Note: This is the parent URL of the site, and not the specific URL of the list you are trying to query.

In the Navigator dialog box, on the left side place a check mark next to the list you wish to query and click Load:

The SharePoint List will be retrieved and a data model will be created in Power BI Desktop:

 

Once loaded, on the right side of Power BI Desktop will contain your data – specifically the columns in the SharePoint list:

Note: The data will contain columns that are also hidden on SharePoint.

Now, let’s create a simple pie chart to show how many hours are spent on each project. Click the pie chart sign , and a new pie chart will be placed in the workspace:

On the right side, drag the columns Title to the Details field and Hours Spent to the Values field:

In the workspace, notice the pie chart that was created and updated in real time:

Next, create a bar chart by clicking the icon. Drag Project
to the
Axis
field and drag Hours Spent to the Value field.

Notice the bar chart is created in real-time:

Next, on the toolbar click Publish:

When prompted to save, save the Power BI Desktop file to your desktop and give it a name, for my example I will use Time Tracker. At the Publish to Power BI dialog box select My Workspace and click Select:


The report will now be published to Power BI, once finished open an internet browser and navigate to www.powerbi.com and sign-in:

On the left side, expand My Workspace and click the report Time Tracker. This will display the report you just created in Power BI Desktop:

To create a dashboard based on this report, hover the mouse cursor over the pie chart and click the (thumb tack icon). At the Pin to Dashboard dialog box, in the New dashboard field type a name (for this example I will use Time Tracker) and click Pin:

This will create a new dashboard and a toast notification will appear, close the message:

Repeat the above steps for the bar chart. Hover the mouse cursor over the bar chart and click the (thumb tack icon). At the Pin to Dashboard dialog box, in the select Existing Dashboard ensure Time Tracker is selected and click Pin:

Next on the left side, click Time Tracker underneath Dashboards to access the dashboard that was just created:

This will display the dashboard. Click Share at the top right corner

 

In the Share dashboard flyout on the right side, in the Grant access to field type the name of a user and select that user. Then click Share:

A new toast notification will appear indicating the dashboard has been successfully shared:

The user will receive the following email message, giving them a link to access the dashboard:

IMPORTANT: This dashboard can also be accessed using the Power BI smartphone app.

For fun, let’s use the natural language Q&A to ask a question about the data. On the dashboard page, in the Ask a Question about your data field type how many totals hours spent and press Enter. In real-time the sum of the total number of hours will be shown:


Let’s ask another question, repeating the step above ask how many hours spent on project eclipse:


Click Exit Q&A. Click on the pie chart, the report view will open. Click the red area of the pie chart. Notice how in real-time the data is filtered on both the pie and bar charts to reflect filtering on Project Health:

Conclusion: As you can see, it’s relatively straightforward, fast and easy to connect to your SharePoint list and create powerful visualizations of your data and share with others using PowerBI.

As always, if you have feedback, comments on this post or ideas for future posts please let me know in the comments below. Also, I would love to hear how you are using PowerApps, SharePoint and PowerBI to digitally transform and create new scenarios.

 

PowerApps: Be a hero and transform business process, in 10 minutes (and without writing code!)

Introduction: This post will walk you thru how to create an application using PowerApps in 10 minutes.

Background:
PowerApps is a powerful tool from Microsoft that allows anyone (yes anyone) to build an application that connects to your existing data sources, that can run on iOS/Android/Windows Phone/Windows devices or in a web browser – all without writing any code. This is pure awesomeness, because you can solve business problems with real solutions, and drive real results.

The data you can connect to can range from Excel spreadsheets, SQL databases, SharePoint lists, Dynamics 365 and even non-Microsoft applications such as Salesforce or Dropbox, and custom APIs (full list of sources PowerApps can connect to can be found here)

Scenario: The possibilities are endless. In this blog post we’ll use a scenario of time tracking and the challenge of having to enter that time into a system of record and make the interaction with the data as frictionless as possible. For this example, we’ll use SharePoint Online as that system of record, and will build a PowerApp to enter that data. For bonus points in a follow up post, we’ll explore how to visualize and make sense of that data using Power BI. (Of course, this is just an example – what’s an example from your business that you can use to build your first PowerApp?)

For the example, I’ll make it real by sharing with you this is a similar solution my team at Microsoft uses to keep track of time spent on projects and programs we manage and provide reporting on how we spend our time up to our leadership. This simple solution provides an easy way to not only perform data entry but also enables a very interesting way to provide visibility of the data to others outside the team who may not be familiar with the details.

Let’s do this!

To start, we’ll take an existing SharePoint site that has a list the team uses on a daily basis to perform time entry. This list contains the following columns:

  • Title
  • Date
  • Project
  • Hours spent
  • Team Member

Note: SharePoint is powerful and allows you to create lists that can perform data lookup from other sources (i.e. a drop down menu that looks up data in another list) and even personalize this for the user so they can only see time they entered. For purposes of this blog and example, I’m going to keep it simple and use free form text fields, date, and a few radio buttons. But I encourage you to play around with this and explore!

Here’s a screenshot of the list with columns:


And a view of the list, in data entry view on SharePoint:


Now, back to the SharePoint list, on the toolbar you will notice a menu for PowerApps. Click that menu and select Create an app:


On the flyout on the right, in the Name field type Time Entry and click Create:


A new browser window will launch, and you will be taken to create.powerapps.com. A dialog box will appear, select your country and click Get started:


The PowerApps Designer will be displayed:


At this point, the app can be further customized using the designer, however it is fully functional. To access the app, browse to http://powerapps.com and sign in:


IMPORTANT: You can also access this app by download the app PowerApps on your mobile device. Once signed in, you will see the same list of apps to access.

From the menu on the left click Apps:


Click the app you just created:


A new browser window will open and the web version of the PowerApp will be launched:


Click the + (plus) sign in the app and enter some data then click the check mark to submit the data:


Once the data is submitted, you will be returned to the home screen and can see a history of the data that was entered (note this view can be customized if needed):


Back in SharePoint, we can see the data populated in the list:


Conclusion: It really is that easy to create an easy to use application. Depending upon the use case you may need to use PowerApps Designer to customize the user interface. To see how to visualize and report this data in a dashboard in Power BI see PowerBI: Visualize your data in dashboard in 10 minutes. In addition, you can automate these tasks and workflows using Microsoft Flow, which I will write about in a future blog post as well.

As always, if you have feedback, comments on this post or ideas for future posts please let me know in the comments below. Also, I would love to hear how you are using PowerApps to digitally transform and create new scenarios.