cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnpb
Helper I
Helper I

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!

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@jnpb the solution @v-kalyj-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 YouTube.gif 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

8 REPLIES 8
jnpb
Helper I
Helper I

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 way 

Hi @jnpb ,

What do you mean non numerical characters are through, like this?

vkalyjmsft_0-1663911846899.png

If this is the case, you should right click the column and select Replace Values, replace the character with blank.

vkalyjmsft_1-1663911879476.png

vkalyjmsft_2-1663911900251.png

Hope it helps!

 

Best Regards,
Community Support Team _ kalyj

If 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!

 

parry2k
Super User
Super User

@jnpb the solution @v-kalyj-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 YouTube.gif 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@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 YouTube.gif 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.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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.

vkalyjmsft_0-1663828198424.png

You can add a cusotm column in Power Query:

vkalyjmsft_1-1663828277080.png

Text.BetweenDelimiters([Column1], "$", "For Order")

vkalyjmsft_2-1663828367258.png

Get the result.

vkalyjmsft_3-1663828386799.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If 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: 

Screenshot 2022-09-22 123509.png

Any thoughts on what might be causing this? Thanks so much for the help!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.