Create a column with fixed values based on current month

[Anonymous]

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 ?

[artemus · Accepted solution]

You can use the the below function. Replace Date with DateTime or DateTimeZone depending on the column type.

Date.ToText([DateColumn], "01/MM/yyyy")

[AlB]

@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 

[Anonymous]

Got it, thank you !

[AlB · Accepted solution]

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]

That worked !, thank you. Can you help me understand what that fuction does ? the second column is pretty straight foward however I stil can´t grasp the first one.

[artemus · Accepted solution]

You can use the the below function. Replace Date with DateTime or DateTimeZone depending on the column type.

Date.ToText([DateColumn], "01/MM/yyyy")