Create a column with fixed values based on current month in Powerquery

Question

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 have found the option to add a custom column however I dont know what to put in the code to give me what I want, so I dont have a code to post...

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 ?

Answer 1

Use the following power query function:

= Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Date]) )

And this is the code for the end of month:

= Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Date]) )

Remember to replace the Changed Type statement for your previous step name.

Edit:

Go to Add Column > Custom Column and paste the function to create the first column. Then do the same in order to create the second column. I added an image to guide you.