cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
HanumareddyV Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Issue regarding Calculated Column

Hi @HanumareddyV,

 

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

10.png

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
6 REPLIES 6
ryan_mayu Established Member
Established Member

Re: Issue regarding Calculated Column

@HanumareddyV

 

Have you tried to change the data type in 'Editing Quires'? It works on my end. Thanks.

 

Capture.JPG

Super User
Super User

Re: Issue regarding Calculated Column

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.

 

Untitled.png

HanumareddyV Frequent Visitor
Frequent Visitor

Re: Issue regarding Calculated Column

@ryan_mayu,

Below specified date value is coming from a field, have created a calculated column to get the required part like "Sep1218", after that tried to change the data type and does not work for me.

ryan_mayu Established Member
Established Member

Re: Issue regarding Calculated Column

@HanumareddyV

 

Please try to create another column to transform the data. Hope this is helpful.Thanks.

 

DATE = 
VAR Y=RIGHT(Sheet7[Column1],4)
VAR M=LEFT(Sheet7[Column1],3)
VAR MONTHNUMBER=SWITCH(M,"JAN",1,"FEB",2,"MAR",3,"APR",4,"MAY",5,"JUN",6,"JUL",7,"AUG",8,"SEP",9,"OCT",10,"NOV",11,"DEC",12)
RETURN DATE(Y,MONTHNUMBER,01)

Capture.JPG

 

Community Support Team
Community Support Team

Re: Issue regarding Calculated Column

Hi @HanumareddyV,

 

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

10.png

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
HanumareddyV Frequent Visitor
Frequent Visitor

Re: Issue regarding Calculated Column

Hi Guru,

We have Data in the form of "SimplJan3118sert" ,
where it contains Month(Highlighted in Black),Date(In Red) then finally Year(In Purple)
and i am not able to convert it to Date type as your input.

Anyway thanks for the reply!!!