Thanks @bchager. You really went above and beyond! This definitely solves it. From the perspective of a novice user, it's pretty frustrating that you have to write out such a complicated function to arrive at something that appears so simple (from your solution):
let
// Original text
Source = [#"Login/Access Time"],
// Replace the timezone abbreviation (PST) with its UTC offset (-08:00)
ReplaceTimeZone = Text.Replace(Source, "PST", "-08:00"),
// Parse the text into a DateTimeZone value
ConvertToDateTimeZone = DateTimeZone.FromText(ReplaceTimeZone),
// Remove the timezone information to convert it to a DateTime
ConvertToDateTime = DateTimeZone.RemoveZone(ConvertToDateTimeZone)
in
ConvertToDateTime
It feels like you're fixing something that should been in the app to begin with as a button, am I right (Microsoft)?
At any rate, I ended up converting the data in Excel in a new column using a single formula (applies to both date text fields where there are nonconforming formats):
=VALUE(IF(LEN(C2)=23,LEFT(C2,19),"null"))
Then I just changed the field type to date with appropriate format, and brought back into PowerBI.
Perhaps if I need to connect to a much larger data set where preparing in Excel isn't an option, this formulaic approach will be better. I definitely learned a lot! I really appreciate your time!
