Welcome to the fifth in our Office 365 blog series. In previous blogs we have spoken about why your business needs Office 365, the different mix and match subscription options that are available to you and the benefits of Hosted Exchange, Microsoft Forms and Microsoft Flow.
In this blog we discuss Microsoft Power BI.
Microsoft Power BI is a business intelligence and analytics solution that lets you visualize your data and share insights across your organization, or embed them in your app or website.
You can connect to hundreds of data sources and bring your data to life with live dashboards and reports.
I have previously likened it to “Excel on steroids” when talking about it since it is capable of providing useful business intelligence from many data sources. A word of warning though – there is a learning curve!
Microsoft has produced a short video “introducing Power BI” which can be viewed here.
We need to start by downloading Power BI Desktop which is a free of charge download from Microsoft, you can get it here.
When you have installed the application and opened it you will see the screen below:-
To start to work Power BI needs a data source to analyse.
This can be as simple as an Excel Spreadsheet or as complex as an SQL database.
For this blog I have produced a simple sales report based on a fictional stationary supply company as an excel spreadsheet.
The raw data is shown below:-
Within the data we have 10 different companies, three regions, three sales managers and seven different products.
With such a small data source such as this, simple analysis within Excel itself is not difficult.
But when we open this data in Power BI we can start to see how that analysis can be achieved so much easier.
After choosing the GET DATA option from the Power BI welcome screen we can see all data sources we can connect to.
For this blog we select EXCEL and select CONNECT:-
When the Spreadsheet is opened in Power BI we need to select the Worksheet we want to work with.
When this is done a preview of the data is presented. Assuming we are happy we then click LOAD:-
Once the data is loaded into Power BI we can get to work. The Power Bi desktop has five key areas:-
Let’s start by dragging in the Order Value and Order Date fields from the field area over to the Report Page area:-
We immediately get a graphical representation of our sales total. If we start to use the drill down tools (highlighted in yellow in the image above) we can get breakdowns by Quarter, Month and Day:-
If we then drag the REGION on to the same ‘graph’ area we can see ‘the sales by region’ in addition to the by ‘reporting period’ we initially started with. This is shown in the image below:-
So far pretty basic, unexciting stuff.
Let’s leave this simple report there but create a new one on the same page. This time we’ll drag in the POSTCODE and ORDER VALUE fields into a new area on the reports page section:-
Power BI has identified postcodes as a geographical or location type feature and has presented this on a map with a visual representation of the total order value within each postcode.
Let’s add another table showing ORDER VALUE and ITEM ORDERED and then a separate table showing SALES MANAGER, ITEM ORDERED and ORDER VALUE.
I have duplicated this table and omitted the ITEM ORDERED field to show both totals and details for each sales manager:-
Once we have this data visualised as we wish, we can start to query the data by simply clicking on anything we are interested in.
Where are we selling our filing cabinets? To find out click on filing cabinets:-
What was the large spike in April – click it to see the details:-
When you select an item of interest the visualisations change to allow you to focus on what you are interested in.
Clicking the same item again resets the visualisation.
We haven’t really started with Visualisations yet but these (graphs and charts) can be changed from one type to another – Pie Charts, Line Charts, Bar Charts, Simple Tables, Funnels etc – the easiest way to learn is to have a play with simple data sources.
To help with this we have made the Excel and Power BI files used in the demonstration available for download. You can download the zip file attachment from the bottom of this blog post.
Power BI is being developed by Microsoft at an astonishing rate with a new features being updated monthly. We use Power BI internally and have helped a number of customers integrate Power BI within their organisations.
If you need help with this exciting product then speak to us today to find out how we can help you.
|PowerBI Training.zip||56.17 KB|