Forum Discussion

chadnelson's avatar
chadnelson
Helper I
2 years ago
Solved

Remove semi colon only when inside parentheses

I have data that contains multiple values within a single cell. I am attempting to separate this data, but occasionally there are additional semi colons throughout these values that are not delimiter...
Need Help
Ahmedx's avatar
Ahmedx
Super User
2 years ago

pls try this

= Text.Combine(
List.Transform(
Splitter.SplitTextByAnyDelimiter({"("})([Column 1]),
(x)=> [
    t1 = Text.PositionOf( x,";",Occurrence.First),
    t2 = Text.PositionOf( x,")",Occurrence.First),
    repl = if t1 < t2 and t1<> -1 and  t2<> -1 then Text.Replace(x,Text.Range(x,t1,1),",") else x ][repl]),"(")

chadnelson's avatar
chadnelson
Helper I
2 years ago

Unfortunately that didn't work. It returned the following error:

 

"Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

    • chadnelson's avatar
      chadnelson
      Helper I
      2 years ago

      This worked! Thank you, so much.

       

      = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(
      List.Transform(
      Text.Split([Column 1],"("),
      (x)=> [
      t1 = Text.PositionOf( x,";",Occurrence.First),
      t2 = Text.PositionOf( x,")",Occurrence.First),
      repl = if t1 < t2 and t1<> -1 and t2<> -1 then Text.ReplaceRange(x,t1,1,",") else x ][repl]),"("))