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

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - 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's avatar
d_gosbell
Icon for Super User rankSuper User
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