Extracting text before 3 differents delimiters

[sverdugo]

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

[jhartranft60]

@sverdugo 

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.

[sverdugo]

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

[jhartranft60]

@sverdugo 

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.

[sverdugo]

@jhartranft60 

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

[jhartranft60 · Accepted solution]

@sverdugo 

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!

[sverdugo]

@jhartranft60 

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