Add 2 years to a date column

Hi robstv 

To answer the immediate question, the syntax to add two years rather than convert to the year would be:

=Table.TransformColumns(#"Filtered Rows",{{"MyDate", each Date.AddYears(_,2), type date}})

For Table.TransformColumns, the first item in each "transformation list" (i.e. the three items within the inner curly braces) is the name of the column to be transformed, provided as text.

The second item in each "transformation list" has to be a function that takes a single argument. The original value in the column is transformed using this function.

For example, the functions are highlighted in red in the "transformation lists" below:

{"MyDate", Date.Year, Int64.Type}

{"MyDate", each Date.AddYears(_,2), type date}

• Date.Year is already a function that takes a single argument, so it can be provided as-is.
• Date.AddYears actually takes two arguments, but we want its second argument is fixed as 2 in this case, so we can create a function based on Date.AddYears that takes a single argument by adding each and using the underscore to represent the argument. Actually each Date.AddYears(_,2) is itself shorthand for (SomeDate)=>Date.AddYears(SomeDate,2)

Functions that need to be told which column to act upon normally take the column name as text. For example Table.TransformColumns or Table.CombineColumns.

However, when you have a table (or record), and you want to refer to a column of the table (or field of the record) as an object, you can use square brackets (the "lookup operator") to refer to the column (or field) by name, for example TableName[ColumnName] or RecordName[FieldName].

For me, trial and error and observing code created by the interface is a good way to learn, but all the answers should be under here somewhere:

https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference

In particular

https://docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification

Regards,

Owen