topic Re: Create a new column, extract in DAX Commands and Tips

Create a new column, extract

Oros — Thu, 20 Jun 2024 21:27:36 GMT

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.

Re: Create a new column, extract

vicky_ — Thu, 20 Jun 2024 23:07:01 GMT

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.

Re: Create a new column, extract

Anonymous — Fri, 21 Jun 2024 07:28:50 GMT

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.

Re: Create a new column, extract

Oros — Fri, 21 Jun 2024 14:11:43 GMT

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:

Re: Create a new column, extract

Oros — Fri, 21 Jun 2024 14:17:33 GMT

Hi Anonymous ,

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

Re: Create a new column, extract

Anonymous — Mon, 24 Jun 2024 08:51:01 GMT

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.