Date field with non-uniform patterns

[monojchakrab]

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 having some challenge with the date fields in the original source files.

In each of the source files, the date field is supplied in a different format, like below :

While some other files have a date given as a date-time, but without the "T" in between as above :

A few has given the date as a range as below :

With the result that I cannot split the column with a uniform splitter.

Is there a better workaround to navigate this problem - without the dates from the source files, I am not able to combine successfully nor am I able to link this properly to a date table for other time-intelligent analysis

Any help much appreciated

Thanks

[jennratten · Accepted solution]

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"

[jennratten]

Hello - you can do something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYvBCcAwDMRWMf6mgbMvftRz9Be8/xotFJrmJyQ0pzqcHey0y5HODGtAAlrHqhaCM4flCGlB/Py6Hr9N8H36IgiTLi/StOoG", 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 if Text.Contains ( [Column1], " - " ) then Text.Combine({Text.Middle([Column1], 4, 2), "/", Text.Middle([Column1], 6, 2), "/", Text.Start([Column1], 4)}) else Text.Combine({Text.Middle([Column1], 5, 2), "/", Text.Middle([Column1], 8, 2), "/", Text.Start([Column1], 4)}), type text)
in
    #"Added Custom Column"

[monojchakrab]

Thanks @jennratten.

I think the code works on dates of the types shown, but it throws wrong results for normal dates :

I think we will need another if loop to leave the normal dates, unchanged.

[jennratten]

Hello - this will do it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYtBCsAgDAS/ErzawCZrDs07ehP//w2FQq233Rmm9+JwKqi0x5HODKtAAmVc21oI7myWLaQG8eO7WvyI4Gf0SRAmKu+kndG6YwI=", 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 
        try if Value.Is ( Date.From ([Column1]), type date ) then Date.ToText ( Date.From ( [Column1] ), "MM/dd/yyyy" ) else false otherwise
        if Text.Contains ( [Column1], " - " ) then Text.Combine({Text.Middle([Column1], 4, 2), "/", Text.Middle([Column1], 6, 2), "/", Text.Start([Column1], 4)}) 
        else Text.Combine({Text.Middle([Column1], 5, 2), "/", Text.Middle([Column1], 8, 2), "/", Text.Start([Column1], 4)})
        , type text)
in
    #"Added Custom Column"

[monojchakrab]

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?

[jennratten · Accepted solution]

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"

[monojchakrab]

@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 )

[jennratten]

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.

[monojchakrab]

I am sorry I should have shared this :

[monojchakrab · Accepted solution]

Thanks @jennratten That worked like a charm! Thanks for making the time.