Create a new column, extract

[Oros]

Hello,

I have a payment terms column. How do I create a column based on the payment terms column to show the percentage if available, if no percentage available, then put zero as the value.  Thanks.

[Anonymous · Accepted solution]

Thanks for the reply from @vicky_ , please allow me to provide another insight:

Hi @Oros ,

If you want to achieve the result with calculated column, you can try below formula and change [result] column type by whole number.

result = 
VAR Terms = 'PaymentTerms'[TERMS]
VAR PercentPosition =
    SEARCH ( "%", Terms, 1, LEN ( Terms ) )
VAR SpacePosition =
    SEARCH ( "S", Terms, 1, LEN ( Terms ) )
RETURN
    IF (
        CONTAINSSTRING ( PaymentTerms[TERMS], "%" ),
        MID (
            PaymentTerms[TERMS],
            SpacePosition + 1,
            PercentPosition - SpacePosition - 1
        ),
        "0"
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oros]

Hi @Anonymous ,

Thank you for for help.  It looks fine but just getting that small error that I could not figure out.  Thanks again.

[Anonymous]

Hi @Oros ,

Based on the description of the error message, please check the data column classes of the columns referenced in the mid function to make sure they are all of type text.

related document:

MID function (DAX) - DAX | Microsoft Learn

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vicky_]

This can be done in Power Query (assuming that the format of your data is SOMETHING space number%)

First part is to extract the number part by going to Add Column > Extract > Text between Delimiters

Then you convert the column to a Whole Number, this will result in some errors. For the errors (i.e if there's no percentage), you can go to Transform > Replace Values > Replace Errors to make them 0.

[Oros]

Hi @vicky_ ,

Thank you very much for your quick reply.  It looks like just a little bit of tweak will bring the desired result.  I am still NOT getting the actual number that is attached or equivalent to the percentage.  Any ideas?  Thanks again.

Here are the details: