Extract text before delimiter in fabric using DAX.

[Creature]

Hey All,

I have a column that is ticket numbers like SCTASK0012345, WKR0012345 etc. I need to extract all the text before the numbers to create a column for "Ticket Type". This data set comes from SNOW - SQL SERVER - POWER BI - FABRIC. I cant edit it in the desktop version and upload so I need the DAX expressions to do this in fabric.

Update: The below works sort of but it now returns as WRK0,INC0 etc. I tried putting a K,C in place of 0 but Returned in error.

TicketType =

LEFT

     ('query1' [Number],

      Search(

        "0",

        'query1'[number],

      ))

Thanks!

[Sahir_Maharaj]

Hello @Creature,

Can you please try this approach:

TicketType = 
VAR FirstDigitPosition = 
    MINX(
        FILTER(
            GENERATESERIES(1, LEN('query1'[Number])),
            MID('query1'[Number], [Value], 1) >= "0" && MID('query1'[Number], [Value], 1) <= "9"
        ),
        [Value]
    )
RETURN
    LEFT('query1'[Number], FirstDigitPosition - 1)

[Laxmanjatoth]

Step 1 -- go to power query 
step-2 

step -3 in there a option is extract click on that.

step : 4 select  last characters 

step : 5  enter how many ending characters to keep .

let me know if you still help 


Best,
Laxman babu 

[Jihwan_Kim · Accepted solution]

Hi,

Try something like below whether it suits your requirement.

It is for creating a calculated column.

TicketType =
LEFT ( query1[Number], SEARCH ( 0, query1[Number] ) - 1 )