Between two numeric there is a special character which need to be replace with hyphen

[aqeel_shaikh]

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

[dufoq3 · Accepted 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

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

[aqeel_shaikh]

Hi, I did try combining both and it worked, below is the final out put.

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

[wdx223_Daniel]

=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?