Help with parsing a date, time and timezone column

[mouse_art]

I have a column containing string dates such as "10 Sep 2024 3:51 PM CEST", which I want to convert to a DateTimeZone colum:

= Table.AddColumn(#"Changed Type", "date", each DateTimeZone.FromText([Modified Date], [Format="<format string>"]))

The problem is that I'm unable to find the format string. I think what I'm missing the the token to represent the timezone identifier. I've tried many combinations, such as "dd MMM yyyy h:mm tt", but I keep betting errors such as:

DataFormat.Error: We couldn't parse the input provided as a DateTimeZone value.
Details:
    Text=06 Dec 2024 7:22 PM CET
    Format=dd MMM yyyy h:mm tt
    Culture=

Any suggestion?

[Einomi]

@mouse_art 

I have tried to suggest a solution, but could find the thread to reply

You can try this

= Table.AddColumn(#"Type modifié", "date", each 
    let
        txt = [Raw],
        dt = DateTime.FromText(Text.Start(txt, Text.Length(txt) - 5)),
        tz = Text.End(txt, 4),
        offset = if tz = "CEST" then 2 
                 else if tz = "CET" then 1 
                 else 0,
        dtz = DateTimeZone.SwitchZone(DateTimeZone.From(dt), offset)
    in
        dtz
)

The column Raw contains "10 Sep 2024 3:51 PM CEST" and it adds a column [date] displaying 10/09/2024 16:51:00 +02:00 as datetimezone

If this helps, please consider accepting this as a solution as well and give kudos 🙂

[v-sgandrathi · Accepted solution]

Hi @mouse_art,

Thanks for reaching out!

You're right, Power Query's DateTimeZone.FromText does not support parsing time zone abbreviations like CEST or CET directly using a format string. These abbreviations are ambiguous and aren't currently recognized by the DateTimeZone.FromText parser.

A recommended approach would be to:

• Extract the time zone abbreviation from your string,
• Map it to a corresponding UTC offset (e.g., CEST -> +02:00),
• Rebuild your datetime string in a standard format (like ISO 8601),
• And then pass it to DateTimeZone.FromText.

Hope this helps, If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you for using Microsoft Fabric Community Forum.

[mouse_art]

Thanks! Fortunately, in my data set, I only had CET and CEST, so it was easy to simply apply ReplaceText to "+01:00" and "+02:00". Then the format string ""dd MMM yyyy h:mm tt zzz" worked!