Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
@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.
Solved! Go to 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
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
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
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
=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),"-")
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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |