Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chadnelson
Helper I
Helper I

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 delimiters. I would like to replace all semi colons with commas, ONLY when the semi colon is inside parentheses. Any suggestions?

1 ACCEPTED SOLUTION

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RixzOI0gpuWV6jNLW?e=gWIFbE

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

Share some data to work with and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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]),"(")

Screenshot_3.png

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.

https://1drv.ms/u/s!AiUZ0Ws7G26RixzOI0gpuWV6jNLW?e=gWIFbE

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]),"("))

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.

Ahmedx
Super User
Super User

if there is only a semicolon inside the parentheses then it is simple.

Screenshot_2.png

show an example so we can help you

speedramps
Super User
Super User

In Power query replace ";" with ,

See screen print
I am an unpaid volunteer helper seeking kudos.

Please show your thanks by clicking the Accept Solution button and the thumbs up button to leave kudos.
Thanks

speedramps_0-1697231631301.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.