Power Query: Types of Value

Welcome to our Power Query blog.  This week, I introduce more Value() M functions; Value.Type() and Value.ReplaceType().

I have been looking at how Value() M functions can be useful: this week, I am looking at Value.Type() and Value.ReplaceType.

So first of all, let’s take a look at

Value.ReplaceType either returns a new value with the replacedtype ascribed or raises an error if replacedtype is incompatible with the value’s native primitive type.  In particular, the function raises an error when an attempt is made to ascribe an abstract type, such as any.  When replacing a type of a record, the new type must have the same number of fields, and the new fields replace the old fields by ordinal position, not by name.  Similarly, when replacing the type of a table, the new type must have the same number of columns and the new columns replace the old columns by ordinal position

Surprisingly, there is no description of Value.Type() in the Microsoft help pages, so I will demonstrate the use of Value.Type() with an example which also uses the aforementioned Value.ReplaceType().

I have the monthly expenses for my usual imaginary salespeople for the last year.  The missing entries below are for months that the salespeople haven’t supplied their expenses:

In order to predict what will probably be spent on expenses next year, I want to average out the expenses for this year and put this figure into the months with missing expenses. My first step is to create an average column by selecting the ‘Add Column’ tab.

I select all the date columns by holding the CTRL key whilst I click on each one. On the ‘Statistics’ section of the ‘From Numbers’ section, I can use the dropdown to select ‘Average’.

This gives the following:

I have my Average column (which I have moved so that I can show the null entries too).  I now want to substitute the average value into the null values.  I will do this in two steps.  First, I will substitute the nulls for zeros to get the M syntax for substitution, then I will change the M code to use the Average column value instead.

On the ‘Transform’ tab I can drop down from the ‘Replace Values’ section to access the replace values functionality. If I want to do this for a particular column, I can also right click with the column selected. In this case I have selected all of the numerical columns:

I opt to replace null with zero (0). This will tell me what M functionality to use.

The nulls have been replaced and I can see the M code used.

I need to change this so that the ‘0’ which appears after ‘null,’ is replaced by the Average value.  Since I want to do this for all the rows, the syntax I need is ‘each [Average]’, viz.

The values look great, but the data types don’t. All of my selected columns now have a data type of ‘Any’, and I need them to be numeric. I could just go through each column and change the type, but there is a quicker way. This is where Value.ReplaceType() comes in:

I want to go back to the data types I had when I first inserted the Average column.  Since I am going to compare the types on my current table with the types on the table at that point, it’s very important that both tables have the same number of columns.  If I tried to use an earlier version of the table (say ‘Source’) this would not work (since the Average column didn’t exist).

I have typed a new step:

This takes the types from the table ‘Inserted Average’ and uses those types for ‘Replaced Value’ in my latest version of the table.  When I execute this step, the data types on the numeric columns should change back to the data type they were before I replaced the zeros with the average:

The data types are now back to numeric. If I had changed the data types deliberately, and wanted to return to those data types, then I would use that step instead. It will work correctly as long as both versions of the table have the same columns.

Come back next time for more ways to use Power Query!