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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
mouse_art
New Member

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?

1 ACCEPTED SOLUTION
v-sgandrathi
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
Einomi
Helper V
Helper V

@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
Community Support
Community Support

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.

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!

Helpful resources

Announcements
May 2025 Monthly Update

Fabric Community Update - May 2025

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