Delete last character

[Sylvine_Wyz]

Hello,

I want to create a calculated column.

I'm looking to create a DAX formula that removes the last character.

Example :

order_reference_order	New Order N°
WQ032060Q	WQ032060

I try the formula below but all "Q" are deleted.

New Order N° =

var Order_last_caracter = RIGHT('wyz_reporting bi_order'[order_reference_order],1)
return

IF(

    Order_last_caracter="Q",

   SUBSTITUTE(

       'wyz_reporting bi_order'[order_reference_order],RIGHT('wyz_reporting bi_order'[order_reference_order],1),"" ),

       Order_reference_order
)

Can you help me please ? 

Thanks 

Have a nice day 😄

[Syndicate_Admin]

Hello, the solution would be:

1) You calculate what the length of the field is by subtracting 1 (without the last character):

Length = len(order_reference_order)-1

2) Create the New Order Number field, collecting the number of characters indicated by the length:

New Order No. = left(order_reference_order,length)

Anything you tell me. Best regards,

[Anonymous · Accepted solution]

IF(
RIGHT([order_reference_order], 1) = "Q",
LEFT([order_reference_order], LEN([order_reference_order]) - 1),
[order_reference_order]
)

Try this 🙂 remember to mark as solution if correct. Cheers

[Sylvine_Wyz]

Hello @Anonymous , 

thanks for your help.
I tried your solution but there is an error message :
"Impossible to convert the value from text type to number type"

Have a nice day