Power BI can supercharge your Excel data (part 2)

For the last couple of decades Excel has been the most popular tool to convert raw data into reports and charts. This could change soon as more people start to use Microsoft’s new Power BI. This is the second of three articles, to be published this week, showing how you can use Power BI to get the most out of your data.

Pivot Tables in Excel have been around for two decades and are the tool of choice when summarising large data sets. Power BI builds on the functionality of Pivot Tables with a drag and drop interface that allows you to easily create many different types of reports.

The advantage of Power BI is that it is built to handle large relational databases, so it has some built-in features that you won’t find in a Pivot Table.

Power BI is designed to bring data together from multiple sources. When you do that you open up the possibility of identifying relationships that otherwise would be hard to see.

Accountants tend to have a good grasp of most of the data available within an organisation and as such we can build or assist in the creation of flexible dashboards to enable users to learn more about their own areas.

Following on from the previous post where we used the Get Data option to import a simple CSV file, in this post we will take that data and create some simple reports.

Terminology

In Power BI, fields are used to manipulate the data. In Excel terms fields are columns.

The Power BI reporting interface

The Power BI screen is straightforward – see image below and the numbered descriptions. You will be pleased to note that there is an Undo option on this screen.

Figure 1

  1. Working area: this is where you create the reports and charts. It is a free form area much like PowerPoint. You can move the reports and charts around wherever you want.
  2. There are three icons representing three different views.

    Chart icon (top): this is the main reporting view where you see the reports and charts.

    Grid icon (middle): this the data view. Like Excel, it has rows and columns and tables are shown in separate tabs.

    Flowchart icon (bottom): this view shows the relationships within your data. It lists the fields (columns) in each table and how they are related. In our case there are no relationships as we have a single table. 
  3. Visualizations: these are all the reports and charts we can create. You can download more reports and charts, of which many are free. The list of reports and charts is expected to expand over time.
  4. Fields: each table and its fields is listed. Fields are listed in alphabetic order.
  5. Page tabs: these are just like Excel. You can create separate pages in your report. Double click the page name to change it. The plus sign adds a new blank page.
  6. Control icons: these two icons (there can be three icons in some cases) allow you to control and format the report and charts.

    Dotted Table (left icon): this allows you to control what is in the reports/charts plus how it is displayed and filtered.

    Paint Roller (second icon): this allows you to control the formatting of the report and charts (you may have noticed we have upgraded from a paint brush in Excel to a roller in Power BI).

    Magnifying Glass (third icon, not shown): this provides analytic options.

Visualizations

We will focus on two types of Visualization reports. Working with Visualizations is similar to working with Excel Pivot Tables. You can drag fields between different areas to change the report or chart layout.

Matrix

This is Power BI’s version of a Pivot Table. 

Figure 2

It doesn’t yet have all the functionality of an Excel Pivot Table but features are being added regularly. 

Note: there is a Table Visualization to the left of the Matrix icon. It is limited in how it can display data and is only useful for basic reports. The Matrix report is more versatile.

Multi-row card

This Visualization is ideal for displaying and highlighting key numbers: e.g. Total Sales or Total Profit.

Figure 3

Creating a report

If you know what type of report you want, first select it and then tick the boxes in the Fields section to include them in the report. You can drag the field names around to change the layout of the report.

Defaults

If you don’t select a report before clicking a field then Power BI will use a default. The common defaults are:

  • value fields default to a Column Chart
  • state or region fields default to a map – yes it knows states/regions are geographic
  • text fields default to a Table report
The screen shot below shows a simple report and its settings using our small data set.

Figure 4

As you can see, this is similar to the Excel Pivot Table interface.

A note about clicking

The options in the Visualizations section will vary depending on what you click on in the working area. If you click the white space you can set a filter for the whole page.

Be careful when creating new reports. Click the white space first before clicking the Visualization icon. If you have a report selected and you click the new icon you will change that report. Remember you can use Undo.

Professional Development: Learn more about Power BI and unlock the true potential of your data at the Unlock Excel conference. Seven of only 110 Microsoft “Most Valuable Professionals” (the world’s leading Excel gurus), will demonstrate how to present a clear and convincing story from even the most complex data. Events will be held in Melbourne, Brisbane, Sydney and Auckland.

Getting your cards on the table

The Multi-row card allows you to highlight total amounts. There is also a Card Visualization that highlights a single figure. An example of the Multi-row card is shown below.

Figure 5

Tweaking the matrix

Below are a couple of useful changes you can make to the Matrix report.

The current report lists the values and totals. You can modify this to instead show percentages.

If you select the Matrix report and then right click the Amount field in the Visualizations section you will see the options below.

Figure 6

As you can see there are a number of calculation options, but the one we require is Quick Calc which is a relatively new option. The Quick Calc interface is shown below.

Figure 7

This is similar to Excel’s Show Values As option in Pivot Tables. Unfortunately it only has one option, Percent of grand total, but hopefully the number of options will expand over time with new software updates. If you select that option and click OK the Matrix report is modified as shown below.

Figure 8

Another use of this option is to show both the value and the percentage. In our report above, that would make the report too large to be useful. I have modified it by removing State from the Column section and adding the Amount field to the Values section again – see image below.

By the way, you can do the same trick in Excel’s Pivot Tables – have a field listed twice in the Values area – one with a value and the other using the Show Values As option.

Figure 9

Filtering

You may have noticed the Filter section in the Visualizations area. There are filter options in that Filter area. You can also filter by report or the whole page.

There are two ways to filter – either use the Filter section or add a Slicer to the page. The Filter section allows you to set the filters for the current page or for the whole report (all the pages). The Filter section is more a set and forget filter. Slicers are interactive.

Slicers allow more interactive reports. A Slicer (which was added to Excel 2010) is a graphic filtering interface that is easier to use than Excel’s typical filtering options. It is not a report but it filters the reports/charts on the page. Power BI Slicers are different from Excel Slicers

Below I have added a Slicer and added the State to it.

Figure 10

So far I haven’t looked at the Formatting options (the Paint Roller icon). The reason is that there are many formatting options and I will leave you to play with those. You may have noticed that so far all the default formats have been reasonably acceptable. They are certainly better than Excel’s Pivot Table defaults.

In the case of Slicers, the Format option handles some functionality options.

The Selection Controls section under Formatting allows you to change how the Slicer interface works. You can add a Select All option and turn off Single Select, which makes it easier to select multiple items – see image below.

Figure 11

If you leave Single Select turned on then you have to use the Ctrl key with the mouse to tick multiple items.

Going forward

The Power BI interface can be a bit overwhelming at first because there are so many icons and formatting options. Focus on a few at a time and have a play. Remember you are not affecting the data in any way and there is always the Undo option.

In the next post I will look at charts. 

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to a4@iinet.net.au

Read next: How Power BI can supercharge your Excel data (part 3)


April 2017
April 2017

Read the April issue

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

PURCHASE CONTENTS