Forum Discussion
Extracting $amount from Notes field
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.
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.
Hi there,
Thank you for this! Using this I achieved partial success. Below are two values where one worked, and one that didn't. I'm unsure why it wasn't successful for every entry:
Any thoughts on what might be causing this? Thanks so much for the help!
