Forum Discussion

Oros's avatar
Oros
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Create a new column, extract

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.  Than...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

vicky_'s avatar
vicky_
Icon for Super User rankSuper User
2 years ago

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's avatar
    Oros
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    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: