Forum Discussion
Create a column with fixed values based on current month
Hello, I am pretty new to power query and I am trying to add a column that is filled as "01/MM/YYYY" for every row. Based on a check of the current date. I.e today is 20/01/2021 so the column would be filled with "01/01/2020" for all the rows.
I found the option to add a custom column however I dont know what to put in the code to give me what I want.
And next I would like to fill another column but considering the past year, using the same example, today being 20/01/2021 I want the column to be filled with "01/01/2020" so, subtracting a year from current date.
Any clues how to do so ?
You can use the the below function. Replace Date with DateTime or DateTimeZone depending on the column type.
Date.ToText([DateColumn], "01/MM/yyyy")Hi Anonymous
For the first colum:
= Date.StartOfYear(Date.From(DateTime.LocalNow()))For the second:
Date.AddYears([Column1], -1)Note it references Column1, the first column we've created
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
5 Replies
You can use the the below function. Replace Date with DateTime or DateTimeZone depending on the column type.
Date.ToText([DateColumn], "01/MM/yyyy")Hi Anonymous
For the first colum:
= Date.StartOfYear(Date.From(DateTime.LocalNow()))For the second:
Date.AddYears([Column1], -1)Note it references Column1, the first column we've created
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
1st column: reads the current Date and time, converts it to date and extracts the first day of the year the date is in
2nd column simply takes the date from the previous column and subtracts one year
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers