Forum Discussion

Oros's avatar
Oros
Post 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.  Thanks.

 

 

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.

     

     

5 Replies

  • vicky_'s avatar
    vicky_
    Super 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
      Post 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:

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

     

     

    • Oros's avatar
      Oros
      Post Prodigy
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.