Forum Discussion
Remove date format from text column
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
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..)
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
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)
You can prepare new example with as many situations as possibl and we can try...