Forum Discussion
Helper IIIBetween two numeric there is a special character which need to be replace with hyphen
dufoq3
Between two numeric there is a special character which need to be replace with hyphen
Eg: 111/2222***333$444
solution: 111-2222-333-444
Please help with a query solution.
Hi aqeel_shaikh,
Result
Define CharsToReplace
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ01DcCAi0tLWNjYxUTExOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), CharsToReplace = "/*$", Ad_Cleaned = Table.AddColumn(Source, "Cleaned", each [ a = Text.ToList([Column1]), b = Text.ToList(CharsToReplace), c = List.Zip({ b, List.Repeat({"-"}, List.Count(b)) }), d = Text.Combine(List.ReplaceMatchingItems(a, c)), e = Text.Combine(List.RemoveItems(Text.Split(d, "-"), {""}), "-") ][e], type text) in Ad_Cleaned
6 Replies
- wdx223_Daniel
Community Champion
=Text.Combine(List.TransformMany(Splitter.SplitTextByCharacterTransition(each not List.Contains({"0".."9"},_),{"0".."9"})(111/2222***333$444"),each Splitter.SplitTextByCharacterTransition({"0".."9"},each not List.Contains({"0".."9"},_))(_),(x,y)=>y),"-")
- aqeel_shaikh
Eg: 111/2222***333$444
is a sample, i have many number with different special character... how do i write query for all in one go.. Appreciate your help?
- dufoq3
Hi aqeel_shaikh,
Result
Define CharsToReplace
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ01DcCAi0tLWNjYxUTExOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), CharsToReplace = "/*$", Ad_Cleaned = Table.AddColumn(Source, "Cleaned", each [ a = Text.ToList([Column1]), b = Text.ToList(CharsToReplace), c = List.Zip({ b, List.Repeat({"-"}, List.Count(b)) }), d = Text.Combine(List.ReplaceMatchingItems(a, c)), e = Text.Combine(List.RemoveItems(Text.Split(d, "-"), {""}), "-") ][e], type text) in Ad_Cleaned- aqeel_shaikh
is this the correct query
let
Source = Excel.Workbook(File.Contents("C:\Users\s441801\OneDrive - Emirates Group\General - AQEEL\Power query\POWER Q TEST SAMPLE.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Ad_Cleaned = Table.AddColumn(Sheet1_Sheet, "Cleaned", each
[ lst = {"0".."9"},
a1 = Splitter.SplitTextByCharacterTransition(each true, (x)=> not List.Contains(lst, x))([Column1]),
a2 = Text.Combine(List.RemoveItems(a1, {"-"})),
b1 = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains(lst, x), each true)(a2),
b2 = Text.Combine(List.RemoveItems(b1, {"-"}))
[b2],
CharsToReplace = "!@#%^&/\*$_",
a = Text.ToList([Column1]),
b = Text.ToList(CharsToReplace),
c = List.Zip({ b, List.Repeat({"-"}, List.Count(b)) }),
d = Text.Combine(List.ReplaceMatchingItems(a, c)),
e = Text.Combine(List.RemoveItems(Text.Split(d, "-"), {""}), "-")
][e], type text)
in
Ad_Cleaned- dufoq3
You see that it is not, but you are mixing 2 separate topics and creating a mess.
