Need help in power query

[aqeel_shaikh]

My sample data is
1234-567-AB$C#DEF-123

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

[AlienSx]

Text.Combine(
    List.Transform(
        Splitter.SplitTextByEachDelimiter({"-"})("1234-567-AB$C#DEF-123"),
        (x) => Text.Combine(List.RemoveItems(Text.ToList(x), {"-", "#", "$"}))
    ),
    "-"
)

[aqeel_shaikh]

@AlienSx - Hi, I have multiple lines so how i would use the above to get the result...any alternate?

[AlienSx]

Table.TransformColumns to change existing column or Table.AddColumn to create a new one is totally up to you

let
    lines = #table ({"lines"}, {{"1234-567-AB$C#DEF-123"}, {"1234-567-AB$C#DEF-123"}}),
    rm = Table.TransformColumns(
        lines, 
        {"lines", (x) => Text.Combine(
            List.Transform(
                Splitter.SplitTextByEachDelimiter({"-"})(x),
                (y) => Text.Combine(List.RemoveItems(Text.ToList(y), {"-", "#", "$"}))
            ),
            "-"
        )}
    )
in
    rm

[aqeel_shaikh]

@AlienSx - i have above data but the query doesn't work for them.

[dufoq3]

Hi @aqeel_shaikh, I remember that I helped you to solve such some similar situations. You have no # or $ in you new sample which is totally different from what you've asked in 1st post. Like last time - you started creating a mess. I recommend you to provide sample with as much examples as you can and expected result based on sample data (do not provide sample as screenshot - if you don't know how to provide sample data in usable format - read note below my post). Then we can help you to create a formula which covers your issue at once.

[aqeel_shaikh]

Hi @dufoq3, The data has various senario's. yes the data i have received is a mess hence needed help to develp a query which covers all the scenario's including which i have provided in my 1st post.

1st Scenario - if between Alphabet there is special character, Special character will need to be removed completely
2nd Scenario - If between Alphabet and Numeric there is special character, Special character will need to be removed completely.

3rd Scenario- If between Numeric there is special Character, will need to replace it with "-" hypen.

above scenario's are included in the huge data im processing. let me know if i can share this to you on as an email?.

Thanks,

[dufoq3]

You can share sample with us here as table or via google drive with public permissions etc.

[dufoq3 · Accepted solution]

Check this:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdTJbtswEAbgdwly7FizcTtKdougtV0DElqgaZCnyPt3LFKKOT3q489tSOr19QkJsMSBEJiBnt6+dKSNNBITpJ/DPDOJNAuJIVSjXC0RxdX+fiBynJ+ZOOxNGajGP82GdVZstM2oThVCpAT0jpaqC0pIfJmhBIUbYyWi73RZgCLBtQmf9LJ8Xemm1VhOxVJrz410jGtHfKBv6FKi3GRZ4SS5lDIw1q3+mCTcrl1L/P38iZdMmXEQiNtnER3upT5iq14zBtQjYnCWRtuzN8slZ/fxtjlESYYME2I+Hnt7sdF+nZulQveltH7XcSYMaZHJVlLrfZNRVPEM9UAWwZBHiHb+GeHP1K4JkRIWOKSA8wxC0652pIfIAYzjuC2ENdus1uwheEgOwn+QHSSX4Ig9qMYeCooDQg/dLOxXaiC0VzmHtaTcg1XCgzpgn2DxUHrghwOv4Lsw+4SSB58IfpbkE8klyn5pd9grFmT9pSCnc0cZ7HJ2xF3IHuxgD9N+HuP10XJ+IUZpNiMfou357R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Transformed = Table.AddColumn(Source, "Transformed", each 
        [ numbers = {"0".."9"},
          alphabets = {"a".."z", "A".."Z"},
          a = Text.ToList([Column1]),
          b = List.Select(a, each not List.Contains(alphabets & numbers, _)), //Characters to remove/replace
          c = Text.Combine(List.Transform(a, each if List.Contains(b, _) then "-" else _)), //replace other special characters with "-"
          d = Text.Combine(List.RemoveItems(Splitter.SplitTextByDelimiter("-")(c), {""}), "-"), //remove extra dashes
          e = Splitter.SplitTextByCharacterTransition(each true, (x)=> List.Contains(alphabets & numbers, x))(d), //split any|text
          f = Text.Combine(List.Transform(e, each if List.Contains(alphabets, _, (x,y)=> Text.StartsWith(y,x)) then Text.Start(_,1) else _)),
          g = Splitter.SplitTextByCharacterTransition((x)=> List.Contains(alphabets & numbers, x), each true)(f), //split text|any
          h = Text.Combine(List.Transform(g, each if List.Contains(alphabets, _, (x,y)=> Text.EndsWith(y,x)) then Text.End(_,1) else _))
        ][h], type text)
in
    Ad_Transformed

[aqeel_shaikh]

this worked thanks

[dufoq3]

You're welcome.