Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Extract Date into a seperate column

I have a column of names followed by dates and there is not standardization so I can't split on a delimter.  When I was a .NET programmer I would turn the string into an array, cycle one by one,  tes...
Need Help
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    You could also do this in PowerQuery by using the Add Custom Column option using the following to split on the first location of a digit. The first expression will get everything after the first digit, the second one gets everything before the first digit. (of course this will do strange things if people have a digit in their email address...)

     

    Text.Range([Column1], Text.PositionOfAny([Column1], {"0".."9"}))

     

    Text.Range([Column1],0, Text.PositionOfAny([Column1], {"0".."9"}))

     

    Below is a full query that you can paste into a blank query to see it working:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVXAqyi/PU9BVMNQ3MtI3MjA0U4rViVbKTs3JqdRLzs8rT6x0yE3MzAGycxWA0pa6Bka6hsYhhmZWpoZWBsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOfAny([Column1], {"0".."9"})),
    Custom1 = Table.AddColumn(#"Changed Type", "Date", each Text.Range([Column1], Text.PositionOfAny([Column1], {"0".."9"}))),
    Custom2 = Table.AddColumn(#"Custom1", "Name", each Text.Range([Column1],0, Text.PositionOfAny([Column1], {"0".."9"})))
in
    Custom2

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

You could also use Text.Select( ) as follows -

 

Text Custom Column formula - Text.Select([Column1],{"A".."Z","a".."z",".","@"," "})

Date Custom Column formula - Text.Select([Column1], {"0".."9","/","-","T"})

 

For Date one, you can then just extract text before the delimiter "T". Some minor clean up after that needed, and then convert to Date.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    6 years ago
    mahoneypat wrote:

    You could also use Text.Select( ) as follows -

     

    Text Custom Column formula - Text.Select([Column1],{"A".."Z","a".."z",".","@"," "})

    Date Custom Column formula - Text.Select([Column1], {"0".."9","/","-","T"})

     

    Yeah, I initially thought of using Text.Select(), but the issue with the examples is that the first example would then include a leading "-" character. And if the name also had a capital "T" in it you would get that captured by the date pattern also... It's tricky with such variation in the data and with a limited set of examples.