Forum Discussion

mouse_art's avatar
mouse_art
Regular Visitor
1 year ago
Solved

Help with parsing a date, time and timezone column

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?

Need Help
  • v-sgandrathi's avatar
    v-sgandrathi
    1 year ago

    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.

3 Replies

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
      mouse_art
      Regular Visitor
      1 year ago

      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!

  • Einomi's avatar
    Einomi
    Icon for Helper V rankHelper V
    1 year ago

    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 šŸ™‚