Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
monojchakrab
Resolver III
Resolver III

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 :

monojchakrab_0-1680511471161.png

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

 

monojchakrab_1-1680511641051.png

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

monojchakrab_2-1680511711547.png

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

 

 

2 ACCEPTED SOLUTIONS

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_0-1680599457649.png

 

View solution in original post

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

View solution in original post

9 REPLIES 9
jennratten
Super User
Super User

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"

jennratten_0-1680514913517.png

 

Thanks @jennratten.

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

monojchakrab_0-1680518844986.png

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

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"

jennratten_0-1680525277294.png

 

This takes care of most of the formats @jennratten , except this one which I just noticed in the date column :

monojchakrab_0-1680530462314.png

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"

jennratten_0-1680599457649.png

 

@jennratten - when I am trying to change the type to Date, some of the values are returning an error...

monojchakrab_1-1680673236849.png

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 :

monojchakrab_0-1680805077761.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors