Implicit Measures

Protected: A Finance Team’s Guide to Pivot tables Implicit Measures

Implicit Measures

Previous Session Summary

In the previous lesson, we looked at the idea of Grouping, which is a useful function to allocate values in a column in the connected dataset to a new group which we can then use in the Pivot Table. This can be a useful timesaving function as the alternative is manually putting the information in the connected dataset, perhaps using an IF function.

We also looked at how we can sort values in the Pivot Table from Low to High and vice versa.

Lesson Synopsis

The whole purpose of Pivot Tables is to aggregate or summarise data. The field of data that you want to summarise, you put in the Values quadrant of the Field List.

You don’t think about it, but by putting data in that Quadrant, the Pivot Table automatically applies an aggregation function like a SUM or a COUNT or an AVERAGE to all the data in that field. If the information in the field is numerical then a SUM is applied, if not then a COUNT is used. This automatic calculation is called an Implicit Measure.

By putting a field into the Values Quadrant, we ‘implictly’ request the Pivot table to ‘measure’ the information in that field.

In this lesson we look at this concept in more detail as well as how the Pivot Table filters the information that the Implicit Measure can use to produce its number.

Resource Listing

The Video

Play Video

0:32 - What is the 'Source' step in the 'Query Settings' Pane?
00:58 - Where is the Formula Bar and what is M Code?
01:37 - What is the Changed Type step?
01:47 - What do the icons in the Headers mean?
02:55 - How can I remove unnecessary rows?
04:03 - What is the little cog to the right hand side of some steps in the Applied Settings pane?
04:39 - What is Promote Headers?
06:00 - How can I load the data back into Excel?

Lesson Notes

The first two steps that have been created in the Query Settings pane are ‘Source’ and ‘Changed Type’.

The Source step tells PowerQuery where to find the information that you want to bring into the PowerQuery Editor. ‘Changed Type’ on the other hand dictates to PowerQuery, the format that our data should be in in each column.

There are a number of different datatypes in PowerQuery including Text, Whole Number, Decimal Number, Date, DateTime, Percentage, True/False (Boolean) and a few more. Having the correct datatype is much more important in PowerQuery than it is in Standard Excel. For example:

a) the Text, Date, DateTime and Number types have sets of functions that you can only use with each datatype.

b) assigning incorrect datatypes can result in cell errors e.g. assigning number a column which has text in some cells will result in errors in the cells where text exists. We’ll look at this in a later lesson.

Next we look at how to add our first manual command to PowerQuery.

We frequently need to remove unncessary rows, and we can do this either at the top or the bottom of the dataset using the Remove Rows option in the Home tab. This is the first command that we manually give to PowerQuery to transform the dataset and it will remove the unnecessary rows at the top of our PO dataset. After that we want to move the top row of data into the Column Headers position. To do this, we use the Use First Row as Headers option in the Home or in the Transform tab.

Finally we then put the resulting dataset, all cleaned and formatted back into the Excel workbook using the Close and Load option in the Home tab.

.... and a little more!

The Formula Bar – The Formula Bar does not automatically come up in the PowerQuery Editor the first time that you enter the Editor in Excel. To bring the Formula Bar up, go to the View tab. In the Layout section of the Ribbon, tick the Formula Bar box.

Remove rows – While we remove the unnecessary top rows in the video, many system reports also have totals lines at the bottom of the reports which can cause a problem when we’re doing things like summing our data later once we’re finished in PowerQuery. We also have the option to remove a specific number of bottom rows using the Remove Rows drop down in the Home tab, as well as removing alternative rows, duplicate rows, rows with errors and blank rows.

Use First Row as Headers – In the video we promote the first row in the table into the headers row. However we can also take the Headers and put them into the first row. To do this go to the same Use First Row as Headers drop down in the Home or Transform tab and select Use Headers as First Row.

Previous Lesson

Back to Course

Next Lesson