Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Cannot convert '1/13/2013' to date value


Hey guys, noob here. Cnanot find solution on net. Can anyone help..

Community Support
Community Support
Super User
Super User

You can use the optional culture argument with Table.TransformColumnTypes(). Using "en-US" will interpret the text as "mm/dd/yyyy" whereas other cultures like "en-IN" will interpret the text as "dd/mm/yyyy".


For example, this should shouldn't throw an error:

    Source = #table({"Date"}, {{"1/13/2013"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US")
    #"Changed Type"

 It would if you used "en-IN" instead.

Super User
Super User

As requetsed by @@AlexisOlso
if you cant Click here to download the solution 

The in Power BI Desktop clickc Home / Get data / Blank query / Advance editor

and replace the  the query with this text 

which get the day, month and year sections of the text and then reassmbles them to a date.

Please click thumbs up and accept as solution. Thank you 


Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNY3MjA0VorVAfIwuEYInpm+OZQTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date In" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date In", type text}}),
#"Get day" = Table.AddColumn(#"Changed Type", "Day", each Text.BetweenDelimiters([Date In], "/", "/"), type text),
#"Get month" = Table.AddColumn(#"Get day", "Month", each Text.BeforeDelimiter([Date In], "/"), type text),
#"Get year" = Table.AddColumn(#"Get month", "Year", each Text.AfterDelimiter([Date In], "/", 1), type text),
#"Change text to numbers" = Table.TransformColumnTypes(#"Get year",{{"Day", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}}),
#"Get date" = Table.AddColumn(#"Change text to numbers", "Date out", each #date([Year],[Month],[Day])),
#"Change format to date" = Table.TransformColumnTypes(#"Get date",{{"Date out", type date}})
#"Change format to date"



Super User
Super User

Try this


Click here to download the solution 


Thanks for reaching out for help.

I put in a lot of effort to helped you, now please quickly help me by giving kudos.

Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and ttechniques, not do the users job for them !

So plaase click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

Not everyone has permission to download files onto their computer. Please consider at least summarizing your solution in your answer rather than posting a link to a file without any explanation.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors