Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.