Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 text,for example Mr A & Mr B or Miss C Mr D.
What i need is to remove the names from the data , so that no personal details are available in the text.
i tried the below logic , but it didnt remove any of the names. is there any way to resolve this.
let
// Original text from the column
OriginalText = [Exception Reason],
// List of prefixes to check
Prefixes = {"Miss ", "Mrs ", "Mr ", "Ms "},
// Check if the text starts with any of the prefixes
StartsWithPrefix = List.First(List.Select(Prefixes, each Text.StartsWith(OriginalText, _)), ""),
// Remove the prefix if it exists
RemovePrefix = if StartsWithPrefix <> "" then
let
// Remove the prefix from the text
TextWithoutPrefix = Text.RemoveRange(OriginalText, 0, Text.Length(StartsWithPrefix))
in
Text.Trim(TextWithoutPrefix)
else
OriginalText
in
RemovePrefix
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
you have several names in one line, post an example of several lines to help you
i have created some sample examples on how it will be seen in the below screenshot for your reference
Thank you so much for your solution.
I tried the three solutions but i am getting the below errors for the reasons where the names are not available. but it works for the ones with the salutation, but if there are multiplenames it wont be replaces. could you able to help with that
This is an example of what i am trying to achive, i need to find the names and replace it with a " * " so that names are removed, names will always be starting by Mrs, Miss, mrs, Ms which can help to find the names in the text
Can you please share your demo input and expected output!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
56 | |
43 | |
28 | |
22 |