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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MartianCactus
Regular Visitor

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

image_2022-07-30_135551597.png

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

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support
AlexisOlson
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:

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

 .

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.