Forum Discussion

Icon for Helper IV rank

Helper IV

3 years ago

Solved

Delete last character

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 😄

Tutorial Requests

Anonymous
3 years ago
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

3 Replies

Anonymous
Not applicable
3 years ago
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
  Helper IV
  3 years ago
  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
Syndicate_Admin
Administrator
2 years ago
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,