Forum Discussion

Youri98's avatar
Youri98
Helper I
2 years ago
Solved

Remove date format from text column

Hi!

 

Is it possible to remove a date format from a column? For example this:

 

Date: 01-01-2023 Apple: 25.000 euro.

 

I would like it to remove everything but the value '25000'.

 

I used Text.Select([Apple],{"0" .."9"}) to remove everything but numbers, as the column usually doesn't contain a date as well. So, this works in most cases. But in some cases, when it does contain a date, it gives me the value '0101202325000'. Would it be possible to remove the date as well? 

 

Thanks in advance!

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi, no, it tooks text after last delimiter ":" in previous solution.

    I had to use different logic now.

    Result

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDPSgQxDIdf5cecFHaWOrII3gRPnr3IOoc6k3EK3WZo0129+RwePfsU+iY+iZk/gisIPaQh+fIl221R4iYngfSE+2xMdVFtjDFzuEJiOEHLlMKcEvR2TxBGpB1rZMOz9C48FvVqhM1VV8PgqTxYG1vC59vXy+vHO6rNWsk4aXg3aNfpXDqOmFDKgNCTKLFFGqhx1qPpbbSNUEw4cPYtHggpd51rHAVBx1HNXQIHOhK4tkIwZ6W+ylTn+C10+WNCOfJ6sbjjHFXFZ3Eclln6HVdV/r/LHbPwZ+zCvtXb7q3PhMl0urWnTsDdFLfatS7q+hs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Value = Table.AddColumn(Source, "Value", each List.Select(Text.Split(Text.Remove([Column1], {"'", ","}), " "), (x)=> List.ContainsAll({"0".."9", "."}, Text.ToList(x))){0}?)
in
    Ad_Value

     

8 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Youri98, you should provide more examples but for this one code below will work.

    Add as custom column (replace [Column1] if needed):

     

    Text.Trim(Text.Select(Text.AfterDelimiter([Column1], ":", {0, RelativePosition.FromEnd}), {"0".."9", "."}), {" ", "."})

     

     

    If you want to convert extracted number to currency directly in same step it could be like this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSbVSMDDUBSIjAyNjBceCghygiJGpnoGBgUJqaVG+nlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Value = Table.AddColumn(Source, "Value", each Currency.From(Text.Trim(Text.Select(Text.AfterDelimiter([Column1], ":", {0, RelativePosition.FromEnd}), {"0".."9", "."}), {" ", "."}), "de-DE"), Currency.Type)
in
    Ad_Value

     

    • Youri98's avatar
      Youri98
      Helper I
      2 years ago

      Thanks for the help! If I understand this correctly it would only remove the date if it's on the left side of the value, right? 

       

      The input in the column differs a lot. Some examples

      - Just the '25000', so it doesn't have to remove anything

      - 'Apple-waarde € 25.000 (company)' so removing text and special characters would be sufficient for this one

      - 'Date 01-01-2023 Apple-waarde: 25.000 euro.' Your solution would solve this

      - 'Apple-waarde: 25.000 euro. Date 01-01-2023' The value is on the left of the date.

       

      These are the 4 options. Would there be a way to only return the value of 25000 in each case?

       

      Thanks in advance! Appreciate the help!

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Hi, no, it tooks text after last delimiter ":" in previous solution.

        I had to use different logic now.

        Result

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDPSgQxDIdf5cecFHaWOrII3gRPnr3IOoc6k3EK3WZo0129+RwePfsU+iY+iZk/gisIPaQh+fIl221R4iYngfSE+2xMdVFtjDFzuEJiOEHLlMKcEvR2TxBGpB1rZMOz9C48FvVqhM1VV8PgqTxYG1vC59vXy+vHO6rNWsk4aXg3aNfpXDqOmFDKgNCTKLFFGqhx1qPpbbSNUEw4cPYtHggpd51rHAVBx1HNXQIHOhK4tkIwZ6W+ylTn+C10+WNCOfJ6sbjjHFXFZ3Eclln6HVdV/r/LHbPwZ+zCvtXb7q3PhMl0urWnTsDdFLfatS7q+hs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Value = Table.AddColumn(Source, "Value", each List.Select(Text.Split(Text.Remove([Column1], {"'", ","}), " "), (x)=> List.ContainsAll({"0".."9", "."}, Text.ToList(x))){0}?)
in
    Ad_Value