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

     

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

       

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        You've asked me to explain how it works in private message so I decided to copy it also here. Maybe it will help to someone to better understand.

         

        To explain how it works - you should study every function I've used. I recommend to check them here. In top left corner you can search for the function - or select one in the left panel.

         

        I've edited my previous code to record which you can expand and see what is every step doing. I've added also my comments int step SolutionRecord.

         

        Expand this record:

         

        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]),
    SolutionRecord = Table.AddColumn(Source, "Value", each 
        [ a = Text.Remove([Column1], {"'", ","}), //Removes ' and , from the text
          b = Text.Split(a, " "), //Splits text into list
          c = List.Select(b, (x)=> List.ContainsAll({"0".."9", "."}, Text.ToList(x))), //Keep only values that contains numbers and . from the list
          d = c{0}? //Transforms first value of the list to text (? means, if list is blank, return null - without this it would return error if list is blank) 
        ], type record)
in
    SolutionRecord