Extract Date into a seperate column

[jwhitf4770]

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,  test if its a number,  if yes keep track of that and then do a split on the index.  Is there a way to do that in Power BI and DAX or Power Query

example data:

Joe Brown - 1/22/2016

kelly.conway@mail.com 2019-02-13T16:51:03

These are the two different formats,  I am open to any and all suggestions.

[mahoneypat]

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]

---

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

[Greg_Deckler]

@jwhitf4770 - I have developed a way to do what you are describing. The general technique is used in BIN2DEC as well as ARABIC quick measures.

Basically:

Column =
  VAR __Table = 
    ADDCOLUMNS(
      GENERATESERIES(1,LEN([OtherColumn]),1),
      "__Char",MID([OtherColumn],[Value],1)
    )
RETURN
  <some operation over __Table>

You can add another ADDCOLUMNS around that to do your test for text or numeric. This can get tricky since ISERROR and IFERROR do not catch type comparisons, which is annoying.

[d_gosbell · Accepted solution]

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