cancel
Showing results for
Did you mean:
Frequent Visitor

Extracting text before 3 differents delimiters

Hi. I have column SKU from which i need to extract any elements before differents delimiters.

I know Extract is the approach but I dont realize how to declare the 3 differents delimiters in order to obtain the IDx column directly.

Any advice Will be welcome.

thanks , Sebastian

 Sku IDx 44D000020062042 44 16921624301446 16 5920530001603 5 3910318800346 3

1 ACCEPTED SOLUTION

Give this a shot:

``````IDX =
VAR TempId = LEFT(Sku[Sku],4)
VAR Search_D = SEARCH("D",TempId,1,0)-1
VAR Search_91 = SEARCH("91",TempId,1,0)-1
VAR Search_92 = SEARCH("92",TempId,1,0)-1
VAR NewId = IF(Search_D>0,LEFT(TempId,Search_D),
IF(Search_91>0,LEFT(TempId,Search_91),
IF(Search_92>0,LEFT(TempId,Search_92))))
RETURN NewId``````

This logic should provide you with what you need.  Hope this helps!  If so, please mark it as the solution!

6 REPLIES 6

What are the 3 different delimiters?  I think it may be easier for us to help you find a solution if we better understand the logic you're trying to build.

Frequent Visitor

Hi

Sorry for the omission.

The delimiters are D, 91 and 92.

I need to get the characters before these delimiters which they always are present starting from the left of the string.

The IDx column shows the result expected.

 Sku IDx delimiter applied 44D000020062042 44 D 16921624301446 16 92 5920530001603 5 92 3910318800346 3 91

One last question: Is there a max # of characters that the IDx can be?  Is it never more than 2 characters before reaching a delimiter?  Never more than 3?  This is important because I believe I have a serviceable solution, but I need to make sure that a delimiter later in the sequence isn't inadvertently caught

For example:  55927465491 - In this example, I don't want the logic to to find the 91 at the end of this sku before it identifies the correct delimiter of 92.  If there's a max number of characters, I can build that into the logic to prevent false-positives.

Frequent Visitor

Hi.   max # of characters that the IDx is 2.

Give this a shot:

``````IDX =
VAR TempId = LEFT(Sku[Sku],4)
VAR Search_D = SEARCH("D",TempId,1,0)-1
VAR Search_91 = SEARCH("91",TempId,1,0)-1
VAR Search_92 = SEARCH("92",TempId,1,0)-1
VAR NewId = IF(Search_D>0,LEFT(TempId,Search_D),
IF(Search_91>0,LEFT(TempId,Search_91),
IF(Search_92>0,LEFT(TempId,Search_92))))
RETURN NewId``````

This logic should provide you with what you need.  Hope this helps!  If so, please mark it as the solution!

Frequent Visitor

MAGNIFICENT! Clean and sharp solution.

I adapted it to my scenario and applied as new column inside powerbi desktop .

Worked perfectly and helped to understand the concept behind.

Thanks a lot.

Sebastian

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors