Forum Discussion
Transformation to Add Days
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}})
6 Replies
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
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.
Please try this instead
= Table.TransformColumns(#"Changed Type",{{"Hiring Effective Date", each Date.AddDays([Hiring Effective Date],1), type date}})
Pat
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.
