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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Kyle2612
Regular Visitor

CUT OF STRING AT END

Hey guys

 

Today I struggled upon an issue i can't seem to resolve on my own. The data I receive consists of a string value which on its own consists of 3 values split by a comma, e.g. ABCD, EFGH, IJKL. However these are not always from the same format. They can also consist of 2 or 3 values, and all differ on its  own. E.g ABCD, EF, GHL can also exist, and so on. I'm looking for a way to cut off everything to the right of the second value, because the third value is not of interest. Because the third value can differ from 2-4 letters, I can't seem to find the right solution. In addition, it needs to stay a string since after removing the 3rd value, i want to split the values to make new rows for every 1st and 2nd value

1 ACCEPTED SOLUTION
Syk
Super User
Super User

First, split the rows by a delimiter.
Syk_1-1708610263913.png
Syk_2-1708610312892.png


This will give you 3 columns (or more depending on your strings). Just remove the 3rd column

Syk_4-1708610434606.png

 


If you're wanting to create rows from these values.... Select both rows and Transform > Unpivot Columns

Syk_6-1708610489371.png

 

 

 

View solution in original post

1 REPLY 1
Syk
Super User
Super User

First, split the rows by a delimiter.
Syk_1-1708610263913.png
Syk_2-1708610312892.png


This will give you 3 columns (or more depending on your strings). Just remove the 3rd column

Syk_4-1708610434606.png

 


If you're wanting to create rows from these values.... Select both rows and Transform > Unpivot Columns

Syk_6-1708610489371.png

 

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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