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