cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryan_b_fiting
Post Patron
Post Patron

Splitting Column by delimiter

Hello Community - 

 

I am looking to split one of my columns by a delimiter (comma), however, some of the text strings use a comma for grammatical purposes.  Is there a way to split a column by comma that is followed by a space and capital letter?  See small sample of data below:

 

Current Data:

First-Aid Provided On Site?Body Part(s)
YesHand(s), (not wrist, fingers, thumb)
YesLower Extremities-Foot, Lower Extremities-Lower Leg, Lower Extremities-Upper Leg
YesBody Systems & Multiple Body Systems
YesUpper Extremities-Hand, Upper Extremities-Thumb, Upper Extremities-Wrist

 

What I need to see once the column is split:

First-Aid Provided On Site?Body Part(s).1Body Part(s).2Body Part(s).3
YesHand(s), (not wrist, fingers, thumb)  
YesLower Extremities-Foot Lower Extremities-Lower Leg Lower Extremities-Upper Leg
YesBody Systems & Multiple Body Systems  
YesUpper Extremities-Hand Upper Extremities-Thumb Upper Extremities-Wrist

 

Is there anyway to acheive this in Power Query so we can properly split the column by the correct comma delimiter?

 

Thank You Community!

Ryan F

2 REPLIES 2
amitchandak
Super User
Super User

@ryan_b_fiting , you have split the column by delimiters, But that is going to create 2 columns for first one , So you might have to remove the comma in first one based on some logic

 

Split Column Power Query: https://youtu.be/FyO9Vmhcfag

@amitchandak thanks for the reply, but this does not provide any value for my issue unfortunatley.  I am fully aware of how to split columns by a delimiter, but I am looking for a more advanced solution that will allow be to properly split based on my original post.

 

Thanks again.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.