Transform from text to date

[Syndicate_Admin]

Hello everyone

I'm working on transforming some data that comes from a file obtained through Google Sheets. The problem I'm facing is that the date field appears as text and doesn't allow me to convert it to date/time format. I have tried several options but without success.

Has anyone had a similar problem or could you help me with a solution to convert this field correctly?

I'm attaching a screenshot so you can see more details of the field I'm trying to transform.

Thank you in advance!

[Anonymous]

Hi,lbendlin and parry2k ,thanks for your concern about this issue.

Your answers are excellent!
And I would like to share some additional solutions below.
After my testing, the M code provided by lbendlin works very well and it successfully converts the text to the corresponding date.

Has your problem been solved?

If you have found suitable solutions, please share them as it will help more users with similar problems.

Or you can mark the valid suggestions provided by other users as solutions.

Thank you very much for your understanding and support of Power BI.

I hope my suggestions  give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian

[Syndicate_Admin]

Thanks, but I think it didn't work for me... I share screenshot.

[lbendlin]

Try with culture

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilNVTAwVghOLdBRMDKwMjW1MjZWcPcNAXKMTJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each DateTimeZone.FromText(Text.Replace([Column1],"GMT ","+00 "),[Format="ddd dd MMM, HH:mm:ss zz yyyy", Culture="en-US"]),type datetimezone)
in
    #"Added Custom"

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilNVTAwVghOLdBRMDKwMjW1MjZWcPcNAXKMTJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each DateTimeZone.FromText(Text.Replace([Column1],"GMT ","+00 "),[Format="ddd dd MMM, HH:mm:ss zz yyyy"]),type datetimezone)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[parry2k]

@Syndicate_Admin maybe convert it to timezone first and then in the next step convert it into date type. You can also do this in one step but first try this and let's if this works.