How to Remove special character from Alphanumeric character

[aqeel_shaikh]

In the case study, i want to remove special character which is "-" hyphen if it is coming between Alphanumeric character.
if it is between numeric should not be removed. 

can you please help me with the query

Sample:

1) XYZ-1-ACD - expectation is XYZ1ACD
2) 123-456-XYZ - expectation is 123-456XYZ.

[dufoq3 · Accepted solution]

Hi @aqeel_shaikh, check this.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiM0jXUdXR2UYrViVYyNDLWNTE10wWKKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Cleaned = Table.AddColumn(Source, "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], type text)
in
    Ad_Cleaned

[dufoq3 · Accepted solution]

Hi @aqeel_shaikh, check this.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiM0jXUdXR2UYrViVYyNDLWNTE10wWKKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Cleaned = Table.AddColumn(Source, "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], type text)
in
    Ad_Cleaned

[aqeel_shaikh]

getting below error

let
Source = Excel.Workbook(File.Contents("C:\Users\General \Power query\POWER Q TEST SAMPLE.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
Ad_Cleaned = Table.AddColumn(Source, "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], type text
in
#"Changed Type"

[dufoq3 · Accepted solution]

Try this:

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], type text)
in
    Ad_Cleaned

[aqeel_shaikh]

@dufoq3  Thanks for the solution. Much appreciated😊

[dufoq3]

You're welcome 😉

[aqeel_shaikh]

@dufoq3 

If i want to add one more query,

e.g: between numeric there is a special character but to be replaced with "-" hypen.

how can i add that to the same above query?

[dufoq3]

Do you want to replace that special character only if it is between numbers?

[aqeel_shaikh]

Only between numeric replace Special character but with "-" hypen.
also remove special character between Alhpabets.

can this be combined with the query provided above

[dufoq3]

https://community.fabric.microsoft.com/t5/Power-Query/Between-two-numeric-there-is-a-special-charact...

[aqeel_shaikh]

will this help me to remove special character between Alhpabets. if there is a different query can you please share?

[dufoq3]

You should provide new sample data with expected result because there can be many variants.

[aqeel_shaikh]

@dufoq3 ,

My sample data is 

1234-567-AB$C#DEF-123

expectation is to remove bold special character in RED and provide this result "1234-567-ABCDEF123"

[AnalyticPulse]

RemoveHyphens =
try this and let me know if this works:
VAR inputText = [YourColumn]
VAR length = LEN(inputText)

VAR result =
GENERATESERIES(1, length, 1)
RETURN
VAR currentIndex = VALUE([Value])
VAR currentChar = MID(inputText, currentIndex, 1)
VAR prevChar = MID(inputText, currentIndex - 1, 1)
VAR nextChar = MID(inputText, currentIndex + 1, 1)
RETURN
IF(
currentChar = "-" &&
(ISALPHA(prevChar) && ISALPHA(nextChar) || ISALPHA(prevChar) && ISNUMERIC(nextChar) || ISNUMERIC(prevChar) && ISALPHA(nextChar)),
"",
currentChar
)
)
VAR modifiedText = CONCATENATEX(result, result, "")
RETURN modifiedText

My blog:
https://analyticpulse.blogspot.com/2024/03/superstore-sales-2022-vs-2023-year-on.html
https://analyticpulse.blogspot.com/
https://analyticpulse.blogspot.com/2024/04/case-study-powerbi-dashboard-developer.html

See my Pins :
https://pin.it/5aoqgZUft
https://in.pinterest.com/AnalyticPulse/