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 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's avatar
Youri98
Helper I
2 years ago

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's avatar
    dufoq3
    Community Champion
    2 years ago

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