Change years in a Date column in Query Editor

[ContabilidadBI]

Hi!

I am working with an example database that in the fact table has a date column with years 1996, 1997 and 1998. How can I change the years of this date column to other years (for example 2005,2006 and 2007) in the Query Editor, maintaining the day and the month? I can do it in DAX, but I have been trying to do it with M for a while and I can't get it to work. Any ideas?

Thank you!!

[v-cherch-msft]

Hi @ContabilidadBI 

You may try to use replace values to change them.

Regards,

[ContabilidadBI]

Hi @v-cherch-msft  thanks for your response,

That's what I tried but unfortunately it only changes it in the first day of the year:

Thanks

[v-cherch-msft · Accepted solution]

Hi @ContabilidadBI 

You may delete the 'changed type' steps in Applied steps.Then use replace values as requested.At last,click 'detect date type' for the column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQHQktLM6VYHRDHCMExgsiY4+RYgDnGcE4sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"1996","2005",Replacer.ReplaceText,{"date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"date", type date}})
in
    #"Changed Type"

Regards,