Power Query replace day with current year.

[PowerBIET]

Please assist me to create a custom column in Power Query that replaces the day with the current year. 

I didn't want to hardcode 2024 using the Replace Values button in the ribbon, cause the year would change after December 31.  

Power BI File Link 

[Anonymous · Accepted solution]

Hi @PowerBIET ,

I can't open your pbix file for company security policy reasons, but I created the dataset myself and tested it. Please follow the steps below:

The output is as below:

Then use this M function:

= Table.TransformColumns(#"Split Column by Delimiter", {{"Date.1", each Text.From(Date.Year(DateTime.LocalNow())), type text}})

Finally, column merging:

 And the final output is as below:

Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3P01FwLCjKzFEwsNBRMLMyNFcI8FWK1cGQMrcyNsYhZWZlZIZTysQEh5ShkZUxRFssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Date", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    #"ReplacedValues" = Table.TransformColumns(#"Split Column by Delimiter", {{"Date.1", each Text.From(Date.Year(DateTime.LocalNow())), type text}}),
    #"Inserted Merged Column" = Table.AddColumn(ReplacedValues, "Merged", each Text.Combine({[Date.1], [Date.2], [Date.3]}, ","), type text)
in
    #"Inserted Merged Column"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnbasha33]

@PowerBIET 
here is the code, you can easily create one for above scenario.
let
CurrentYear = Date.Year(DateTime.LocalNow()),
Source = YourPreviousDataSource, // Replace YourPreviousDataSource with the name of your previous data source
ReplacedDate = Table.TransformColumns(Source, {{"DateColumn", each Date.FromText(Text.Middle(Text.From(_), 1, 10) & "-" & Text.From(CurrentYear)), type date}})
in
ReplacedDate

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[PowerBIET]

Do you know why this expression error is happening? Or can you please update a copy of the PBIX Link.

Here is a link to the file with the error.

= Table.AddColumn(#"Promoted Headers", "Custom", each let
CurrentYear = Date.Year(DateTime.LocalNow()),
Source = Source, // Replace YourPreviousDataSource with the name of your previous data source
ReplacedDate = Table.TransformColumns(Source, {{"Date", each Date.FromText(Text.Middle(Text.From(_), 1, 10) & "-" & Text.From(CurrentYear)), type date}})
in
ReplacedDate)