Forum Discussion
Removing multiple names from a text in a column
I have a column named exception reason which is in text fomat, within this text there will names of people which will be starting by Mr, Mrs, Miss or Ms, there could be multiple names in a single te...
Hi sarathD4s, this replaces first word between two spasec after defined prefixes.
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcyxCsMwDATQXzk85ydKoV2aqaPJIBKVCBzblWVI/j52m02n45737lYtbWQyUwgHCht4nzmbpIhRC/IqQTIoLi2CAu/tZP0/Cu5kK6tERmfKAFZNik+qcXHT4N07ba3j3X7p0brGjKQmEa/ax5d9WaFbT6UDyt/Kpe2mEw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Prefixes = List.Buffer({"Miss", "Mrs", "Mr", "Ms"}),
StepBack = Source,
Ad_Replaced = Table.AddColumn(StepBack, "Replaced", each
[ a = List.Distinct(List.RemoveItems(List.Combine(List.Transform(Prefixes, (x)=> Text.PositionOf([Column1], x, Occurrence.All))), {-1})), //Prefixes positions
b = List.Transform(a, (x)=> Text.BetweenDelimiters(Text.Range([Column1], x), " ", " ")),
c = List.Accumulate(b, [Column1], (state, current)=> Text.Replace(state, current, "*"))
][c], type text)
in
Ad_Replaced
This seem sto be ot working , as it create a table and when opened its just repeats the three values that is given in the screen shot, my spreadsheet will have multiple columns and Exception reason is just one of the column and i want to see the anmes removed in new column next to it
Example