Transformation to Add Days

Anonymous · ‎08-27-2021

Guys, what is wrong with this syntax please? It doesn't generate error but it doesn't make the needed transformation either. What I need to do is to add 1 day over the dates (by transformation not by adding new column)

= Table.TransformColumns(#"Changed Type",{{"Hiring Effective Date", each _, Date.AddDays([Hiring Effective Date],1), type date}})

Anonymous · ‎09-02-2021

Thank you all, I got the reply from another community already by now, appreciate your help.

Anonymous · ‎09-01-2021

Hi @Anonymous ,

It doesn’t hurt to add a new column.

You can add a new column and then delete the original column, rename it to get the date column.

= Date.AddDays([Date],1)

More details you can refer to Date.AddDays - PowerQuery M.

Also refer to my attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jennratten · ‎08-27-2021

You were very close! Here is the script you need. When transforming columns, the function takes a list of transformations that includes the column name to be transformed followed by the transformation to perform. Since the column name is included as the first element in the transformations list, all subsequent references to the column name should use an underscore _ instead of the actual column name.

Table.TransformColumns(Source,{{"Hiring Effective Date", each Date.AddDays(_,1), type date}})

BEFORE (my date column is named Date)

AFTER

Vickar · ‎10-06-2022

thanks a million. I was trying to apply this transformation on 3 columns of a table, and couldn't get it right, as I was again passing the column reference instead of the underscore.