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,...
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,

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,