Forum Discussion
Remove semi colon only when inside parentheses
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Thanks for the response and apologizes for not being clear in my initial post. The entire column contains cells similar to this:
Person A (Company XYZ); Person B (Company 123); Person C (Company A-1; Accounting); Person D (Company B12)
I would like to replace the instances where the semi-colon is inside the parentheses with a comma, to look like this.
Person A (Company XYZ); Person B (Company 123); Person C (Company A-1, Accounting); Person D (Company B12)
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]),"(")or this code
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.Replace(x,Text.Range(x,t1,1),",") else x ][repl]),"(")sorry this code corrected
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]),"(")It is still giving me the same expression error.
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?"
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
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]),"("))