Share via

Convert Date to Current Year

Anonymous
2021-11-18T21:28:49+00:00

I have a column (i.e., Start Date) of dates , all of various years. Is there a way, using Power Query, to create a new column which converts the Start Date to the current year?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2021-11-18T23:33:49+00:00

    Hi,

    Try this M code

    let 

    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), 

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #date(Date.Year(DateTime.Date(DateTime.LocalNow())),Date.Month([Date]),Date.Day([Date])))

    in 

    #"Added Custom"

    Hope this helps.

    1 person found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-11-19T02:42:28+00:00

    To: Cliff Gu

    re: "confirm whether the shared link and the content...can be shared"

    Yes, that is my intent ( to share the files).
    Appreciate your watchfulness.

    NLtL

    0 comments
  2. Anonymous
    2021-11-19T01:05:43+00:00

    Dear Nothing Left to Lose,

    Thanks for sharing the suggestions here.

    It seems that there are some files and folders in the shared OneDrive link you provided here. As our forum is a public forum, I'd like to confirm whether the shared link and the content accessed by the shared link can be shared here directly. If yes, please add your confirmation and I'll remove the shared link after 24 hours if there is no confirmation from you.

    Thanks for your understanding.

    Cliff

    0 comments
  3. Anonymous
    2021-11-18T22:08:32+00:00

    re: convert to current year

    '---

    Nothing Left to Lose https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

    0 comments
  4. Anonymous
    2021-11-18T21:47:40+00:00

    Sure there is, but we need more information on the rules you want to apply? Do you want to simply keep the month and day and change the year to current year? Or some other rule?

    .

    Try the "column by example" feature.

    • after clicking on the button type the first date
    • hit enter,
    • type the next date

    PQ will guess the rule you want to apply by showing it's guesses below. Keep going until it looks like it understands what you want to do.

    .

    https://support.microsoft.com/en-us/office/add-a-column-from-an-example-power-query-cd82c570-8da7-4d70-91a1-3827b5995eab 

    . https://www.youtube.com/watch?v=DYb9kfn-uJI

    0 comments