Forum Discussion
Date field with non-uniform patterns
Here you are - this first extracts the portion of the text before the T and space and then performs the transformations.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYtBCoAwEAO/svSqpcnGHrp/EDx4k/7/GwqCtbdkhrmu5HBlKIunI1xRuQABpL4Oy2posTG2aksVfnxUD58i+Bx9EgIt2zvFORq3QEUwMtjs2FPvNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom Column" = Table.AddColumn(Source, "Custom", each let t = Text.BeforeDelimiter(Text.BeforeDelimiter([Column1], "T"), " ") in try if Value.Is ( Date.From (t), type date ) then Date.ToText ( Date.From ( t ), "MM/dd/yyyy" ) else false otherwise if Text.Contains ( t, " - " ) then Text.Combine({Text.Middle(t, 4, 2), "/", Text.Middle(t, 6, 2), "/", Text.Start(t, 4)}) else Text.Combine({Text.Middle(t, 5, 2), "/", Text.Middle(t, 8, 2), "/", Text.Start(t, 4)}) , type text) in #"Added Custom Column"Thanks jennratten That worked like a charm! Thanks for making the time.
This takes care of most of the formats jennratten , except this one which I just noticed in the date column :
replacing the "/" with "-" is not going to solve it as it has time also in the field. This is a new combination and hence goes into the else loop and that is why is returning such odd results.
Is there a workaround to take care of these types as well?
Here you are - this first extracts the portion of the text before the T and space and then performs the transformations.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYtBCoAwEAO/svSqpcnGHrp/EDx4k/7/GwqCtbdkhrmu5HBlKIunI1xRuQABpL4Oy2posTG2aksVfnxUD58i+Bx9EgIt2zvFORq3QEUwMtjs2FPvNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Added Custom Column" = Table.AddColumn(Source, "Custom", each
let t = Text.BeforeDelimiter(Text.BeforeDelimiter([Column1], "T"), " ") in
try if Value.Is ( Date.From (t), type date ) then Date.ToText ( Date.From ( t ), "MM/dd/yyyy" ) else false otherwise
if Text.Contains ( t, " - " ) then Text.Combine({Text.Middle(t, 4, 2), "/", Text.Middle(t, 6, 2), "/", Text.Start(t, 4)})
else Text.Combine({Text.Middle(t, 5, 2), "/", Text.Middle(t, 8, 2), "/", Text.Start(t, 4)})
, type text)
in
#"Added Custom Column"
Thanks jennratten That worked like a charm! Thanks for making the time.
jennratten - when I am trying to change the type to Date, some of the values are returning an error...
Am I getting anything wrong? Is there a fix for this? I used your code verbatim as below :
AddNewDate = Table.AddColumn(#"Changed Type","New Date", each let t = Text.BeforeDelimiter(Text.BeforeDelimiter([Date], "T"), " ") in try if Value.Is ( Date.From (t), type date ) then Date.ToText ( Date.From ( t ), "MM/dd/yyyy" ) else false otherwise if Text.Contains ( t, " - " ) then Text.Combine({Text.Middle(t, 4, 2), "/", Text.Middle(t, 6, 2), "/", Text.Start(t, 4)}) else Text.Combine({Text.Middle(t, 5, 2), "/", Text.Middle(t, 8, 2), "/", Text.Start(t, 4)}), type text )What error message you are getting? If you click in the white space in the cell with the error the error message will be displayed in the preview section.
I am sorry I should have shared this :