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

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 ! 

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"

 

 

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.

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-quot/m-p/185979

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

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

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.