Forum Discussion
Extracting $amount from Notes field
I have a table with a Notes field that contains varying text, but each with a dollar value expressed as $nnn.nn . The amount varies but always includes decimals.
How would I go about extracting the $nnn.nn for each row so that I cam compare that number against that in another numerical column?
Thank you in advance!
jnpb the solution v-yanjiang-msft provided is basically looking to extract the value between $ and "For Order" and in case it didn't work because that row doesn't meet the criteria, maybe change it to this:
Text.BetweenDelimiters([Column1], "$", " ")✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
8 Replies
jnpb can you share some sample notes data in the table format here so that it can be easily copied to get the solution?
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
parry2k absolutely: here are two examples
1st example:
Order Cancellation credit: Registration 305099, Message: CREDIT/REFUND: Customer 111027, Associated Orders 212227 ============= Credit used: $250.95 for Order: 338726 ============= -$126.00, expired credit from 2019 – Office 2022-01-19 ============= Order Cancellation credit: Registration 1070581, Message: CREDIT/REFUND: Customer 111027, Associated Orders 662067
2nd example:
Order Cancellation credit: Registration 320734, Message: CREDIT/REFUND: Customer 116337, Associated Orders 221787
=============
Credit used: $198.45 For Order: 239259
=============
Order Cancellation credit: Registration 349834, Message: CREDIT/REFUND: Customer 116337, Associated Orders 239259
=============
Order Cancellation credit: Registration 349835, Message: CREDIT/REFUND: Customer 116337, Associated Orders 239259
=============
Credit used: $206.01 For Order: 265792
=============
Order Cancellation credit: Registration 387124, Message: CREDIT/REFUND: Customer 116337, Associated Orders 265792
=============
Credit used: $283.50 For Order: 268098
=============
-$126.00 manual refund for delayed start date at F2K -Naz 9/8/17
=============
Order Cancellation credit: Registration 390855, Message: CREDIT/REFUND: Customer 116337, Associated Orders 268943
=============
Credit used: $283.50 For Order: 270875
=============
One thing your request for samples has me thinking about is the need to be able to address instances where multiple $nnn.nn amounts are in a single table. They need to be combined as a single amount in the result.
Hi jnpb ,
According to your description, I create a sample.
You can add a cusotm column in Power Query:
Text.BetweenDelimiters([Column1], "$", "For Order")Get the result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jnpb the solution v-yanjiang-msft provided is basically looking to extract the value between $ and "For Order" and in case it didn't work because that row doesn't meet the criteria, maybe change it to this:
Text.BetweenDelimiters([Column1], "$", " ")✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
That was more effective for sure - although I'm encountering issues where non numerical characters are sometimes making it through.
Is there a way to strip anything that isn't numerical from the string? That might be the cleanest wayHi jnpb ,
What do you mean non numerical characters are through, like this?
If this is the case, you should right click the column and select Replace Values, replace the character with blank.
Hope it helps!
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I ended up doing this, though the volume of instances where text characters made it through and had to be removed was more than I'd liked - it did help solve for the issue however. Thank you so much!
