Power Query: Value Added

Welcome to our Power Query blog.  This week, I look at the Value.Is() M functionality.

Last week in Power Query: Find the Folder, I used the function Value.Is() when I wanted to create a column that I could filter on.  As promised, this week I look at this function in more detail.

(where value is the data being analysed and type is the data type being tested for).

In the Microsoft help pages, Value.Is() is described as follows:

Value.Is is the function corresponding to the is operator in the formula language.  The expression value is type returns TRUE if the ascribed type of value is compatible with type, and returns FALSE if the ascribed type of value is incompatible with type.

This is a very technical description, so I will give an example of how to use Value.Is():

The first thing to notice about Value.Is() is that it returns a logical result, in this case a Boolean one (i.e. a value that may take only one of two values).  In this instance, that means the function returns TRUE or FALSE. This makes it ideal for dealing with columns such as the one I encountered last week, with entities that cannot be filtered upon or for dealing with columns that have mixed datatypes. For this example, I will be using another Value() M function too, namely Value.FromText():

This function decodes a value from a textual representation, value, and interprets it as a value with an appropriate type.  Value.FromText takes a text value and returns a number, a logical value, a null value, a DateTime value, a Duration value, or a text value.  The empty text value is interpreted as a null value.

I’ll demonstrate Value.FromText() by starting with a date as a character string: 2019/03/13. This also explains why an optional culture can be input: I am using UK dates, but I could specify US culture if I wanted to use dates in the US format.

I am now going to look at a more complex column, to see how I can break it down.

This is typical of what may appear when extracting data from other sources. One column has been used to store the amount, date and expense type. I can break this down into separate columns by using Value.Is() and Value.FromText().

I can use ‘Custom Column’ from the ‘Add Column’ tab to create a new column for the amounts:

The M code I have used is:

 I can repeat the process for the other columns using the following M code for the expense type and date respectively.

and

Then it is just a question of removing the null entries. I can do this in several steps. Firstly, I remove the null rows by using the ‘Remove Blank Rows’ from the ‘Remove Rows’ section of the ‘Home’ tab:

Secondly, I then fill down the Amount column by right clicking and choosing ‘Fill’ and then ‘Fill Down’:

Thirdly, I use ‘Fill Up’ for the Expense Type column.

Finally, I can then filter on Expense Date to remove the ‘Empty’ entries.

I now have my data in three distinct columns:

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