Forum Discussion

monojchakrab's avatar
monojchakrab
Resolver III
3 years ago
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 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

 

 

Need Help
  • jennratten's avatar
    jennratten
    3 years ago

    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"

     

9 Replies

  • jennratten's avatar
    jennratten
    Super User
    3 years ago

    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's avatar
      monojchakrab
      Resolver III
      3 years ago

      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's avatar
        jennratten
        Super User
        3 years ago

        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"