How to use Microsoft Power BI for dashboards and more

Microsoft Power BI, short for business intelligence, is a relative newcomer to Microsoft’s portfolio of products aimed at business users. Employees can use Power BI to analyze all types of data, such as sales, inventory and customer engagements, to name a few. It comes in three main forms — Power BI service (the online Software as a Service version), Power BI Desktop (which runs on the Windows desktop) and Power BI for Mobile (for Windows Phone and Windows-based tablets, as well as Android and iOS devices). Information may flow between all three versions, so a report created in Power BI Desktop, for example, can be shared with the Power BI service as well as with Power BI Mobile.

Although many Power BI functions are also available in Microsoft Excel with the add-on Power Pivot, Power Query, Power View and Power Map tools, Power BI is easier to use and more intuitive. It walks you through the steps required to produce dashboards, reports and charts, transform data, create visualizations and run models.

Getting data

With Power BI, you can pull from many different local and cloud data sources, such as your own Excel or CSV files, Microsoft Azure, comScore, Adobe Analytics, Google Analytics, Salesforce, QuickBooks and lots more. The Power BI service main screen lets you choose to open content packs or import or connect to data from files and databases.

A content pack is typically a combination of data and reports that are ready to be used in Power BI, letting you create dashboards and charts immediately and query the data for specific information. When you connect to an online service that provides content packs, such as Adobe Analytics, you’ll need to provide account information for that service. Importing data and connecting to data sources that aren’t already formatted for Power BI generally require some additional steps to make the data usable in the tool.

Note: Microsoft provides industry samples you can use to test-drive Power BI, which are available in Power BI service and Power BI Desktop.

Exploring and visualizing data

The Power BI workspace is where you create a dashboard and then connect to or import data (or a content pack), which lets you analyze and query the data as well as set a refresh schedule for connected data to keep it current. For the purpose of illustration, we clicked the Samples link on the main Power BI screen, selected the Customer Profitability sample and clicked Connect. The following dashboard appeared automatically.

The Add widget command at the top-right of the screen lets you add images, text boxes, video and Web content to the dashboard. Clicking a chart displays more details about the data, and you can query data that appears on the dashboard to get more specific information or to display current information in a different way. The natural language search feature makes this step easy. In this example, we entered gross margin % by month as a pie chart into the Q&A box (the search field) and pressed Enter. The following chart appeared, which can be added to the dashboard by clicking the pin icon to the right of the Q&A box.

The Quick Insights feature is handy for populating a dashboard or for mining data for insights that are not apparent. To use Quick Insights, click the ellipses next to the dataset of your choice in the Dataset section in the left navigation pane, and then select View Insights. Power BI “powers” through your dataset, eventually returning several interactive visualizations (a portion of which are shown below).

[Related: Why Power BI is the future of Excel]

Reporting is equally as seamless. Just click a dataset in the Reports section in the left navigation pane to display prebuilt reports; you can select tabs at the bottom of the screen to see different reports. Click Edit report at the top of the screen to modify the current report or click the plus sign at the bottom of the screen to create a new, custom report. When you edit an existing report or create a new one, visualization tools appear on the right, which you can drag and drop onto the report workspace. Clicking an existing chart and then clicking a visualization tool changes the chart to the new format.

One feature that many users find highly appealing is the capability to associate quantitative data with geographic locations in a map. Power BI provides tools to create simple maps, tree maps, filled maps (choropleths) and 3D globe maps. A filled map is similar to a simple map but uses a range of color intensities, where more intense colors represent larger values. (An example of a filled map is shown in the Reports screen above.)

GlobeMap is similar to the Power Map tool in Excel, but it uses Bing Maps to create a 3D globe with data points plotted on the surface. You can zoom in and rotate the map, and display animations based on different views of the data. Currently, you need to import the Globe Map visual into Power BI and then incorporate it into a report. (To start the import, click the ellipsis under Visualizations in Reports editing view.)

Of course, Power BI also handles simple to complex data modeling. Although data modeling is beyond the scope of this article, you can learn about the steps involved by following the Power BI Modeling tutorial.

Interacting with Microsoft Cortana

Cortana, the personal assistant feature built in to Windows 10, can access data in Power BI to answer questions by providing numerical values or by returning charts, maps and reports. You need to add your Power BI account to Windows 10 for it to work (in Windows 10, go to Settings > Accounts). In Power BI, select a data source and then select Enable Cortana to access this dataset.

Moving on

Because Power BI performs a serious amount of analysis, you need a fast Internet connection to get a decent user experience and to avoid bottlenecks while running the online service. You may also notice a performance hit during a refresh of large datasets. A free version of Power BI comes with 1 GB of cloud storage per user. The paid version, which currently costs $9.99/month per user, offers 10 GB of storage and lets you add connections to your own data, create and publish organizational content packs, and collaborate through Office 365.