[M language] Transform column type to datetime with 2 string formats?

[loic_bouscaud]

Hi all,

I have a column timestamp as string. In this column we can find two format of datetime dd/mm/yyyy hh:mm:ss (European datetime) and mm/dd/yyyy hh:mm:ss AM/PM (USA datetime).

I want to convert this column data type to datetime, but I can't with two string formats.

I tried to to make a custom column with an IF statement like this:

let
    Source = DocumentDB.Contents("https://avabot.documents.azure.com:443/"),
    history = Source{[id="history"]}[Collections],
    history_MessageHistoryItem = history{[db_id="history",id="MessageHistoryItem"]}[Documents],
    #"Expanded Document" = Table.ExpandRecordColumn(history_MessageHistoryItem, "Document", {"conversationid", "timestamp", "text", "id", "_rid", "_self", "_etag", "_attachments", "_ts", "sender", "recipient"}, {"Document.conversationid", "Document.timestamp", "Document.text", "Document.id", "Document._rid", "Document._self", "Document._etag", "Document._attachments", "Document._ts", "Document.sender", "Document.recipient"}),
    #"Convert Date" = Table.AddColumn(#"Expanded Document", "Custom Timestamp", each if Text.Contains([Document.timestamp], " AM") or Text.Contains([Document.timestamp], " PM") then Table.TransformColumnTypes(#"Expanded Document",{"Document.timestamp", type datetime},"en-US") else Table.TransformColumnTypes(#"Expanded Document",{"Document.timestamp", type datetime},null))
in
    #"Convert Date"

But i get a table in each rows, but the correct TransformColumnTypes is done for the row:

Then I tried to stock the string format in the custom column to use it in the transform column types statement:

let
    Source = DocumentDB.Contents("https://avabot.documents.azure.com:443/"),
    history = Source{[id="history"]}[Collections],
    history_MessageHistoryItem = history{[db_id="history",id="MessageHistoryItem"]}[Documents],
    #"Expanded Document" = Table.ExpandRecordColumn(history_MessageHistoryItem, "Document", {"conversationid", "timestamp", "text", "id", "_rid", "_self", "_etag", "_attachments", "_ts", "sender", "recipient"}, {"Document.conversationid", "Document.timestamp", "Document.text", "Document.id", "Document._rid", "Document._self", "Document._etag", "Document._attachments", "Document._ts", "Document.sender", "Document.recipient"}),
	#"Convert Date" = Table.AddColumn(#"Expanded Document", "Datetime Format", each if Text.Contains([Document.timestamp], " AM") or Text.Contains([Document.timestamp], " PM") then "en-US" else null),
	#"Changed Type" = Table.TransformColumnTypes(#"Convert Date",{"Document.timestamp", type datetime},[Datetime Format])
	
in
    #"Changed Type"

But i get this error: Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

I'm running out of idea, so if someone could help me with this issue I'll be very thankful to him.

Kind regards.

[MarcelBeug]

Select the column, choose transform tab - Date - Parse and adjust the generated code to determine the culture (the if .. then ..else in the code below):

= Table.TransformColumns(Source,{{"Document.Timestamp", each Date.From(DateTimeZone.From(_, if Text.Contains(_,"M") then "en-US" else "nl-NL")), type date}})

[loic_bouscaud]

Thanks @MarcelBeug for your answer.

I tried your to use your code and replaced the "_" by my timestamp column but I still got the same error.

What should I use to replace the "_"?

[MarcelBeug · Accepted solution]

You shouldn't replace the "_" at all.

My intenton was to have you just follow the indicated steps and only adjust the generated code by adding the if .. then .. else part,

But if you want to copy and adjust the code, then change the previous step name and the column reference.

According to your example code, this would be:

#"Convert Date" = Table.TransformColumns(#"Expanded Document",{{"Document.timestamp", each Date.From(DateTimeZone.From(_, if Text.Contains(_,"M") then "en-US" else "nl-NL")), type date}})

[loic_bouscaud]

Thanks a lot for your help, it's working now.
But what does the '_' mean in the formula?
I think it's the solution I was looking for. If I compare the code you send me and the code I made first, there are similar.

[MarcelBeug]

The "_" refers to each value in column "Document.timestamp".

It is part of the syntax related to keyword each (which is actually a function).

Typically, each _ refers to the corresponding values, depending on the function in which it is used.

In Table.TransformColumns it refers to the values in the column with the name in the preceding parameter.

Other examples:

In Table.AddColumn, each _ refers to each record.
If you want to refer to another column, you need to add the column, e.g. _[Col1].

And in this particular case you can also omit the _ and only use the column reference [Col] as a shortcut.

In List.Transform, each _ refers to each list item, e.g.:

List.Transform({1..10}, each _ * 10)

[loic_bouscaud]

Thanks for the explanation.

Also, I have a problem with the formula you gave me.
When I use it with Date.From(DateTimeZone.From()) there are no problem, but I want to keep the time so I replaced Date.From by DateTime.From.

In the Query Editor I got the result I want but when I apply the changes to Power BI Desktop, an error message appear but there are no details in the generated table errors and I lost all the data in my Desktop model.

Is there a way to keep the datetime or must I create two custom columns and then concatenate them?

[MarcelBeug]

At the end of my formula, there is type date. Change that to type datetime and you should be fine.

[loic_bouscaud]

I totally miss it!

Thanks again for all your help.

Regards.