Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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

10.png

 

Regards,

Xiaoxin Sheng

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

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ryan_mayu
Super User
Super User

@Anonymous

 

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

 

Capture.JPG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

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

10.png

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

@Anonymous

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors