Forum Discussion
Solved
Date field with non-uniform patterns
Hey Good people, I am trying to combine a couple of queries into one consolidated table - while all the non-date fields I have managed to combine, by changing the column headers dynamically, I am...
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 :