Forum Discussion

robstv's avatar
robstv
New Member
7 years ago
Solved

Add 2 years to a date column

Hi all,

 

I have some demo sales data in a model which is quite old.  I'd like to use PowerQuery to add two years to a date column.

 

So I added a Transform Get Date step and looked at the syntax, hoping to replace the Date.Year function with Date.AddYears and make the neccessary changes in the formula bar

 

So I checked the M reference for Date.Year and it says the syntax is...

Date.Year(dateTime as datetime) as nullable number

However in the Formula Bar and the Advanced editor this syntax doesn't appear.  There is no (...) after the Date.Year.  Instead I have

=Table.TransformColumns(#"Filtered Rows",{{"MyDate", Date.Year, Int64.Type}})

 

Now I'm stuck.

 

I was expecting something like 

Table.TransformColumns(#"Filtered Rows",{{"MyDate", Date.Year("MyDate"), Int64.Type}})

and hoped I could just replace with 

Table.TransformColumns(#"Filtered Rows",{{"MyDate", Date.AddYears("MyDate,2), Int64.Type}})

 

I've looked everywhere for some guides on syntax here as it differs from the Microsoft guide, but can't see find anything.  Also I can't really see how a columnis referenced in an M function... do I use [MyField] or "Myfield" or {Myfield}

 

Happy to read up but I can't see where to start...

 

Thanks in advance!

Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    7 years ago

    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