Remove date format from text column

[Youri98]

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!

[dufoq3]

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]

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 · Accepted solution]

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]

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

[Youri98]

Goodmorning, again. I've got another question related to this. I encountered a case where it doesn't extract the value. I think it doesn't extract this because there's no space between the value and the |. Is this correct?

This is the text where I would like to extract only 503000. 'Value 503000|G|01'

Do you know how to fix this? In addition to the formula you already created, which worked very well until I got this issue.

Thanks in advance! (again..)

[dufoq3]

Hi Youri, I've updated the code and this one will work for such case, but check also last row. In that case it'll extract only first number.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBNSsRAEIWv8piVwiT0ZAiCO0FQXIsg4yzapGIaetKhu3pGIQvP4dK1p9CbeBIrP8JEEHpRXT/fe1WbzSLBTQwMrgkPUansLMuVUmO4RHAwjNJRaMYUo9Z7Ajt42jmJdPPCtWmeFttlDxu7LtrWUnLQ2peEr/fv17fPD2R5KmScFG7XytTp2NpLDChhgOmZhVgitFQYbVHU2uuCyQccXLQlHgkhVpUpDDWMynlxbgJcQzMDl5oJapXIy1S2xrGh818nFL1LJxf3LnqxYiMb10xa8u1XFf6/y81Z+CM7sW/ltnttI2FwOtzaUsVw1RCXMpUOEndDV67WguyuOrU6yqqsu+5mpe0P", 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.SplitAny(Text.Remove([Column1], Text.ToList(",'")), " |"), (x)=> List.ContainsAll({"0".."9", "."}, Text.ToList(x))){0}?)
in
    Ad_Value

[Youri98]

Thanks again for trying to help me. I probably have too many exceptions to create a formula that always works. It's probable that the data includes a lot of random stuff that I need to remove (everything apart from the value xxx.xxx I need to extract)

[dufoq3]

You can prepare new example with as many situations as possibl and we can try...