• Colin Doyle CFO

Power BI: An analytical view

Accountants often are tasked with tracking the performance of their organization (or client organizations). That process usually requires referring to multiple sources to gain insight on the trends of the organization, its industry, and even its competitors.

Critical data monitoring the pulse of an organization can be summarized in custom format in one (or more) visuals, perhaps in a dashboard such as the one shown in the screenshot "Dashboard Example."

Imagine accessing those results and comparisons automatically refreshed, on demand, from your PC, tablet, smartphone, or even wearable device. That's the power of business intelligence and data visualization. (See the sidebar "Business Intelligence, Data Visualization, and CPAs.")

CPAs can choose from a number of business intelligence and data visualization applications, with the two most popular in the accounting space being Tableau and Microsoft Power BI. Other names to know include Adaptive Discovery, Google Charts, Qlik, Wolters Kluwer's TeamMate Analytics, and Zoho Analytics.

Tableau and Power BI are similar in many ways but have some significant differences, which are detailed in the sidebar "Power BI vs. Tableau."

For more on Tableau, see the accompanying article, "Excel vs. Tableau: See Your Data Differently." That article and this one are not meant to be exhaustive examinations of Power BI and Tableau, but rather introductions to how to use the two software packages.

This article focuses on Microsoft Power BI because it combines data extraction, transformation, and load tools already in Excel (namely PowerPivot and Power Query) with robust modern styles of interactive analysis and visualizations.

Microsoft introduced Power BI in 2014 and continues to make improvements. It connects seamlessly with Microsoft Excel and proprietary products including Google Analytics, Microsoft Dynamics, QuickBooks, and many more.

Dashboard example


Power BI works very well with Excel, as the two Microsoft products complement each other. Excel is a great source of data for Power BI, which can receive data from hundreds of possible sources. Power BI is a great choice for a vast array of visualization options, higher-level analytics, automatic updates upon changes to source data, very large sets of data, and user interaction, among other uses.

While Excel is used mostly for simple analytics, as explained in more detail in the aforementioned "Excel vs. Tableau" article, Power BI allows users to create dashboards and reports with at-a-glance looks generated using data stored on-premises or in the cloud. The software provides a "canvas" for the user, as well as many visualization tools for displaying information to users. Visualizations can summarize data from millions of records instantly. For the first-time user, Power BI has a shorter learning curve than Excel.


Power BI has two user interface tools — the Power BI Desktop (available for installation at and the Power BI service accessible through Microsoft Office 365. (If you do not have a Microsoft account, you can set one up at The Power BI Desktop allows users to create and publish reports from which dashboards can be rendered from the Power BI service.

To illustrate how to prepare a sample report and dashboard in Power BI, this article walks through sample data for a fictitious petroleum company. You can download an Excel file with the data here.

To access the data for the report, open the Power BI Desktop and select Get Data from the ribbon (as indicated by the top red arrow in the "Get Data and Report View" screenshot).

Get data and report view

Before we continue, please note the icons for the three view modes of the Power BI Desktop on the left white bar (as shown in the same screenshot). The top icon in the left white bar is the Report View, which allows users to create reports with visualizations. Next is the Data View, which allows users to see and explore data after it has imported to the Power BI desktop application. The third icon on the left bar accesses the Relationships View, which shows tables, columns, and relationships being used in the current model. It also allows users to create relationships between tables using matching columns.

With the Report View highlighted, click Get Data and select Excel from the dropdown list of options, as shown in the "Get Data and Report View" screenshot.

Next, select and open the "Power BI Sample Petroleum Co Data" Excel file from the location where you save the file (see the screenshot "Opening Data From Excel File").

Opening data from Excel file

Power BI establishes a connection with the file containing the sample data. The "Power BI Sample Petroleum Co Data" file contains 10,188 sample data rows. Click the box to the left of the "All Data" worksheet name. Sample rows and columns from the data file will appear on the right, as shown in the screenshot "Loading the Data."

Loading the data

Select the Load button on the bottom right to load the data in Power BI.

After the data is loaded, field names (which represent the columns in the worksheet data) appear under the Fields section of the desktop (shown in the "Select Fields" screenshot). Note that the fields appear in alphabetical order. Power BI currently has no method of arranging the fields to the order shown in the worksheet (an improvement that will hopefully be made soon, but one possible workaround is to begin the name of each column with the column letter.)

7 views0 comments