Forum Discussion

ContabilidadBI's avatar
ContabilidadBI
Icon for Helper III rankHelper III
7 years ago
Solved

Change years in a Date column in Query Editor

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!!

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

3 Replies

    • ContabilidadBI's avatar
      ContabilidadBI
      Icon for Helper III rankHelper III
      7 years ago

      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's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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,