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

[MartianCactus]

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

[v-yangliu-msft]

Hi  @MartianCactus ,

This is the related document, you can view this content:

https://community.powerbi.com/t5/Desktop/quot-We-couldn-t-parse-the-input-provided-as-a-Date-value-q...

https://community.powerbi.com/t5/Desktop/DataFormat-Error-We-couldn-t-parse-the-input-provided-as-a-...

https://community.powerbi.com/t5/Desktop/HELP-We-couldn-t-parse-the-input-provided-as-Date-Value/td-...

https://community.powerbi.com/t5/Desktop/Can-not-parse-the-date/m-p/2402305

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

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:

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

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

[speedramps]

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 

 .

let
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}})
in
#"Change format to date"

[speedramps]

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 ! 

[AlexisOlson]

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.