Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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?
Solved! Go to 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:
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.
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 🙂
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:
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!