Forum Discussion
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...
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
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]),"("))