Forum Discussion
How to replace last 4 characters with 'X'?
Insert below step
= Table.ReplaceValue(#"Changed Type1",each [FULLNAME2],each Text.Combine(List.RemoveLastN(Text.ToList([FULLNAME2]),4),"")&"XXXX",Replacer.ReplaceValue,{"FULLNAME2"})See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsxJzldIS0vJTklTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FULLNAME2 = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"FULLNAME2", type text}}), Custom1 = Table.ReplaceValue(#"Changed Type1",each [FULLNAME2],each Text.Combine(List.RemoveLastN(Text.ToList([FULLNAME2]),4),"")&"XXXX",Replacer.ReplaceValue,{"FULLNAME2"}) in Custom1
Hi Vijay,
Thanks for your help..but i still have some error when applying this formula in Power Query Editor
For case 1 - add a new column called FULLNAME2
= Table.AddColumn(#"Changed Type1", "FULLNAME2", each Text.Combine(List.RemoveLastN(Text.ToList([FULL_NAME]),4),"")&"XXXX") >> This formula works fine!
If I don't want to add a new column, but to "update" FULL_NAME instead (means directly apply the logic in FULL_NAME column itself), what's the formula? I have found something like Table.ReplaceValue but not sure how the correct syntax..
Insert below step
= Table.ReplaceValue(#"Changed Type1",each [FULLNAME2],each Text.Combine(List.RemoveLastN(Text.ToList([FULLNAME2]),4),"")&"XXXX",Replacer.ReplaceValue,{"FULLNAME2"})See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsxJzldIS0vJTklTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FULLNAME2 = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"FULLNAME2", type text}}),
Custom1 = Table.ReplaceValue(#"Changed Type1",each [FULLNAME2],each Text.Combine(List.RemoveLastN(Text.ToList([FULLNAME2]),4),"")&"XXXX",Replacer.ReplaceValue,{"FULLNAME2"})
in
Custom1