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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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