Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Issue regarding Calculated Column

Hi All, I have tried to get the date value as mm/dd/yyyy from "SEP2118" value which is in text format. Tried to change the data type but not able to do so, Please help me asap. Thanks in advan...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous,

     

    AFAIK, dax formula has datevalue function which can convert text value to date but it only support convert text format date value.

     

    According to your snapshot, I found your filed values seems not same as valid text format date values, I'd like to suggest you do some additional steps to use datevalue to convert original text column.

    Column = DATEVALUE(LEFT([MonthYear],3)&"/"&RIGHT([MonthYear],4))

     

    Regards,

    Xiaoxin Sheng

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom.2", each Text.Middle([Date],3,2)&"/"&Text.Start([Date],3)&"/"&"20"&Text.End([Date],2)),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom2", {{"Custom.2", type date}}, "en-IN")
in
    #"Changed Type with Locale"

Hope this helps.