topic Extract text before delimiter in fabric using DAX. in DAX Commands and Tips

Extract text before delimiter in fabric using DAX.

Creature — Mon, 18 Nov 2024 18:50:03 GMT

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!

Re: Extract text before delimiter in fabric using DAX.

Jihwan_Kim — Mon, 18 Nov 2024 20:13:06 GMT

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 )

Re: Extract text before delimiter in fabric using DAX.

Laxmanjatoth — Tue, 19 Nov 2024 12:45:03 GMT

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

Re: Extract text before delimiter in fabric using DAX.

Sahir_Maharaj — Wed, 20 Nov 2024 13:29:51 GMT

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)