Microsoft Power BI: A cheat sheet for business professionals
The big data revolution continues in earnest and enterprises continue to generate more and more data about every business interaction they can track. Couple this with the volume of data now being generated by devices and sensors connected via the Internet of Things and you begin to appreciate the universal need for better data visualization.
All of this generated data must be transformed into actionable information that decision-makers can use. Data visualizations in the form of dashboards and reports must be developed and shared throughout the enterprise, crossing departmental and sometimes even international lines.
Microsoft’s Power BI suite of data visualization tools is designed to accomplish the transformation of data into useful information. Let’s take a look at what you need to know about Power BI.
- what it is: Power BI is a set of visualization tools that allow users to transform collected data into actionable information for decision-makers.
- Why it matters: In a modern enterprise, the ability to transform data into something useful is paramount for achieving overall success.
- Who it affects: Power BI is a vital tool for any enterprise employee trying to communicate information to management, stakeholders, or other decision-makers.
What it is
Power BI is Microsoft’s suite of data visualization tools. The suite allows users to transform data found within, and even outside of the enterprise. Power BI can tap into numerous data sources, including SQL Server, Excel, SalesForce, and Google Analytics.
Why it matters
The ability to transform the huge amount of data generated by business enterprises into some form of actionable information that decision-makers can use on a regular basis is critical to the success of the enterprise. Power BI provides tools for transforming raw data into information that be presented in dashboards, documents, and reports.
Who it affects
Anyone in an enterprise who needs to communicate information to their department, managers, vendors, or to the enterprise, in general, can use Power BI’s data visualization tools to make it happen. Power BI has a number of templates to get even the most technology-challenged user started, and Microsoft provides many free educational training videos that can help turn novice users into experts.
How to get it
Power BI is available in several ways. The most obvious is as a free web service users can access with a standard web browser. The free service is limited to cloud data sources and simple dashboard sharing, and it’s restricted to individual use. The free service is limited to 1GB of capacity per day.
The Power BI Pro service is subscription-based. The Pro version allows users to access all supported data sources and is designed for business use. This version is limited to 10GB of capacity per day.
A desktop version of Power BI is also available for download. And enterprises with large mobile workforces can use the mobile version of Power BI, which is available on Windows 10, Apple iOS, and Android platforms.
For Office 365 subscribers, Power BI is available at no additional cost as part of the productivity suite. Users with the appropriate Office 365 subscriptions and administrative credentials can also download a desktop or mobile version of Power BI to use at their workstations or on their mobile devices. Power BI and its data sources are administered at the enterprise level, so you may need to ask your IT department for access.
How to configure an Excel file for use in Power BI
One of the simplest ways to take advantage of Power BI’s data visualization abilities is to link it with an Excel workbook – it’s the way most business users will employ the Power BI tool. But there are some key tips to keep in mind before you attempt to link your Excel workbook. This quick tutorial will show you how to prepare your workbook for Power BI.
Flatten your data
For Excel power users, this may come as a bit of a shock, but workbooks destined to be linked to Power BI for its data visualization tools must be restricted to flat data. That means no matrix views and especially no pivot tables.
Take a look at the example Excel workbook shown in Figure A. It is a list of stocks and some common data points typically associated with stocks: shares, prices, gain/loss, etc. Notice that there are no summed columns or rows, just column headings.
In general, an Excel workbook like this one would have conditional formatting, summed columns, and so on. If you have a workbook you want to link to Power BI with those kinds of analysis already embedded, you will have to deconstruct it until you get down to a pristine list of flat data.
The next step is to convert your flat data into the standard Excel table format. The easiest way to do this is by highlighting the data in your workbook and pressing Ctrl+T. Or if you prefer, you can click the Format As Table icon in the Ribbon of the Home Tab. Make sure the box that indicates your data has column headers is checked. This procedure will allow you to convert your flat data into a standard Excel table as shown in Figure B.
This last step is optional but it is recommended as a way to reduce confusion in the future. You should name your table something descriptive that you will recognize and understand later. Highlight the cells of the table, click the table name box and give it a new descriptive name.
Now your Excel workbook is ready to be linked to Power BI. If you do not prepare your workbook this way Power BI will just ignore it, so this process can’t be avoided.
How to create your first Power BI dashboard
With a few clicks Power BI can transform your raw data into a visual dashboard that informs the reader and reveals information that otherwise might not have been shrouded by the white noise of enterprise data overload.
The first thing you need to do when creating a dashboard is to access the data. For this example, we will get our data from an Excel workbook. Load up Power BI inside Office 365 and then click the Get Data button located at the bottom of the left navigation pane, as shown below.
One of the choices will be Files. Click that button and proceed to the location where your Excel file is stored. Find your file, point to it, and click the Connect button, shown in Figure B.
It will take a few minutes to process, depending on the size of the workbook. Once it’s finished, you will be presented with a blank Power BI workspace. On the left navigation pane, click the name of the dataset you just imported. The blank workspace will then change into an active dashboard creation tool. It should look something like Figure C.
Create your dashboard
Similar to a paint program, Power BI has icons that represent the various data visualization tools you can use for your dashboard. It also displays a Fields section, where you can choose what data you want to work with at any given point.
The example is a portfolio of stocks, so one useful visualization might be to show the market value of each stock in relation to the whole portfolio – and the most common way to do that is with a pie chart. Click the checkbox for the stock name and the market value and then click the pie chart icon. Power BI will automatically create a pie chart, similar to the one shown in Figure D.
Another important visualization for our stock portfolio would be to see where we have a gain versus where we have a loss. A bar chart would work for this. Click the checkbox for stock name and gain/loss and then click the bar chart icon to create a visualization of gains and losses (Figure E).
Click the save icon in the toolbar to reserve this dashboard report for later viewing. By tying the dataset to an Excel workbook, our dashboard will change when the data changes in the workbook.
How to harness OneDrive to keep your Power BI dashboard fresh
One of the primary benefits of working in a cloud-based environment is the ability to access data with any device and any application at any time. Connecting to data stored in the cloud means that data can always be up to date when you access it, so as long as there is an internet connection, you will always be up to date.
In Power BI you can create dashboards and reports that will always contain the latest data by connecting to a file stored on OneDrive. When you or your team update a file on OneDrive, the changes are disseminated to everyone with access to that cloud storage. Changes on OneDrive can also be communicated to apps like Power BI, saving time and increasing overall productivity. Connecting to data on OneDrive with Power Bi is similar to connecting to any other file, but there are some additional procedures to keep in mind.
To connect an Excel file located on OneDrive, follow the steps we showed you above. Only this time, choose a different location. In Figure A, I can choose from Local Files, OneDrive (Personal), OneDrive for Business, and a SharePoint Team Site.
For the purpose of keeping the data connected to Power BI up to date, the two OneDrives and the SharePoint location would all work. In this example, we connected an Excel workbook on the OneDrive for Business server. Be sure to choose the Import connection.
Using the sample data of a simple report of salespeople and their respective sales of products and geographical areas (Figure B), I created the simple Power BI report shown in Figure C.
Because the data connection was made to a file on OneDrive, any changes made to that Excel workbook will automatically flow to the Power BI report. Note: The default refresh rate for Power BI is one hour. To see more immediate results, you will likely have to refresh the data manually by clicking the Refresh button on the Ribbon of your report.
Just by connecting to data stored on OneDrive or SharePoint, users can keep their Power BI reports and dashboards continuously updated automatically. This not only saves time, but it also eliminates the need to remember to update reports that need to be disseminated to a team, department, or even an entire company. You can’t get much more productive or efficient than that.
How to share your Power BI dashboards and reports
The advantage of sharing Power BI dashboards versus mass distribution is that you can target and tailor each report for a specific individual, group, team, department, or entire enterprise if necessary. Once you have created your Power BI dashboard, sharing is not difficult but there are some steps involved and a few caveats to consider.
Learning to share
The key thing to remember about Power BI sharing is that it is domain-based. In other words, the Power BI dashboard is created under the allonline365.com domain, it can be shared only with other email addresses in that domain. It is important that the enterprise IT department and Office 365 administrators understand this limitation and plan accordingly.
To share a dashboard, first open Power BI. In this example, I am using Office 365 version. Next, navigate to the dashboard you want to share. Right-click the dashboard name in the navigation or click the Share button on the tab bar in the upper-right corner. Either method will take you to a screen where you can list email addresses of the people you want to share this dashboard within your enterprise. It should look something like Figure A.
As you can see, you can send a message with each invitation, which will be a good place to explain what you are sharing and why. Also, note the two checkboxes located near the bottom of the screen. The first lets you grant permission to recipients to re-share your dashboard with others. The second will send an email to each invitee with a link to your shared dashboard.
Any invitation recipient not already registered with a Power BI account will have to register before they can accept and view the dashboard. Once your Power BI dashboard is shared, you will have access to a Shared Dashboard Admin screen, where you can see which users have accepted your invitation and which have not. This admin screen will allow you to revoke a user’s shared status when needed.
Because you can create separate dashboards using the same dataset, you can tailor each report to specific users and share only the information each user needs to see. This can greatly decrease the amount of data clutter users must wade through on a regular basis.
How to download and install Power BI Desktop
The application can be accessed as a web service for free, but it is most often acquired as part of the Office 365 productivity suite. While the online version does a fine job, there are occasions when users want to work on their data visualizations offline on their desktop. This requires them to download the Power BI Desktop tool.
There are two ways to start the download process: Go to the Power BI Desktop webpage and click the download button or click the download button located under the down-arrow tab in the online Office 365 version of Power BI. Either way, you will download a .msi file to your PC. Run this file once the download is complete.
After accepting the license agreement, you will be able to choose where you want to install the application. The installation process will take about a minute.
Start the app
Note that the first time you start the application it will go through an initialization process. When that is finished, you will be asked to sign in to your Office 365 account. To share reports and dashboards you must be logged in.
After logging in, you will be presented a Power BI workspace similar to the online version (Figure B). From here you can import data and create reports and dashboards, then share them with your colleagues.
How to change default visualization formats in Power BI
Using the default settings for each graphical type is fine as a starting point for reports. But for the final report, you are going to want to add personal touches, highlight specific data points, and maybe even add some eye-catching flair to the project. You have access to numerous settings, configurations, and adjustments inside the Power BI palette.
Format and configuration
The default format settings for most users are not going to properly communicate what you want to communicate. Click the report you want to format, which will change the visualization palette dynamically to match your focus. To change the formatting of a report or a particular data point, click the paint roller icon in the visualization palette as shown in Figure A.
Depending on what you have clicked on the report, the Format section will present you with various configuration options. To change the color from the default shown in our example, just click the paint roller and then the Data Colors item. You can change all the colors together or, if you click the Show All slider, you can change each data point individually. If you want to highlight the Facebook data point, for example, because it is your greatest gain, you can change the color to something more striking, as shown in Figure B.
If you can’t find the color you want, just click the Custom Color item and choose your own color from the available spectrum.
It’s your choice
Scroll down the Format list further to make changes to titles and data labels, add a legend, or even add a background if you wish. The choices you make at this point will determine how well your report disseminates what is important to whom.
How to add custom visuals to your Power BI reports and dashboards
Disseminating information derived from business intelligence data doesn’t have to be a dry and uninspired exercise. In some cases, effective communication is going to take some unconventional outside-the-box thinking.
Power BI developers looking for more interesting and compelling ways to communicate their message may find what they seek in the Custom Visuals section of the Microsoft Office Store. Finding, downloading, and inserting custom Power BI visuals is not difficult.
The first step is to point your web browser to the Microsoft Apps page and then click the Power BI Visuals item in the left navigation menu. As you’ll see, the list of vetted and approved Power BI custom visuals is extensive and varied. See Figure A.
Thoughtfully scan through the list of custom visuals to find the one, or perhaps two, you feel will be most helpful for the dataset in question. Keep in mind, these custom visualizations were created by the Power BI community and new entries will populate the list from time to time.
For this example, we will download the colorful World Cloud custom visualization. Click on the link to the app description and then click the Add button to start the download. Be sure to save it in a folder you can reach with the Power BI application. To use the custom visual, click the ellipses in the Power BI Data Visualization Tools palette, as shown in Figure B, and then click Import A Custom Visual in the list.
Navigate to the location of your custom visual and open the file. You should get a dialog box confirming that your visualization was successfully imported. A new icon should appear in your Data Visualization Tools palette, as shown in Figure C.
Click the new icon and then enter the fields you want to include in your new visualization. Each Power BI custom visual will require a specific set of data points to prove effective. Figure D uses the World Cloud visual to show the relationship of each stock’s gain to the rest of the stocks in the portfolio. As you can see, Facebook is dominant in this example.
Expand your toolset
Once you download a custom visual you can insert it into other projects. Depending on the data involved and the message you are trying to convey, there may be a Power BI custom visual that can make the difference between highly effective communication and not-so-effective communication. The community-driven custom visuals found in the Microsoft AppSource are great tools you can’t afford to ignore.
Resource Credit | TechRepublic