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

Building an array from an HTML message

Hi

 

The below is extracted from a field in SharePoint Online used to summarize the result of a webstore purchase. 

 

I am trying to extract the four red highlighted text elements (Device Type, Qty, Qty x Unit Price, Total amount) and store them in an array.  

 

Any pointers on achieving this would be really appreciated!  

 

- **Subject:**  _Web Store Purchase Order_;  

&nbsp;- **Comments or special instructions:**&nbsp; _<p>Comments from Requester for consideration</p>_;&nbsp; 

&nbsp;- **Customer:**&nbsp; _XYZ Inc_;&nbsp; 

&nbsp;- **Service:**&nbsp; _XYC Inc Webstore Purchase_;&nbsp; 

 

&nbsp;- **Android mobile phone x 1:** _1 x 500 = 500_; 

&nbsp;- **Desktop PC x 1:** _1 x 600 = 600_; 

&nbsp;- **USB Charger x 1:** _1 x 30 = 30_; 

&nbsp;

&nbsp;- **Total:** _1130_;

 

Thanks

Warren.

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @Warren_Gibbs :

First of all, I do not recommend that you use Power Automate to achieve this requirement.There two reasons:

1\Incur additional costs:We usually use regular expressions to parse strings, and using similar services in flow requires calling a third-party charging connector.

2\More complex

I will first provide a solution to this problem using Power APPS. If you don’t mind the above two points, then I can provide you with a solution based on power automate.

In Power Apps(Canvas):

1\Create a variable -TheHTMLText

 

Set(TheHTMLText,"- **Subject:**&nbsp; _Web Store Purchase Order_;&nbsp; 

&nbsp;- **Comments or special instructions:**&nbsp; _<p>Comments from Requester for consideration</p>_;&nbsp; 

&nbsp;- **Customer:**&nbsp; _XYZ Inc_;&nbsp; 

&nbsp;- **Service:**&nbsp; _XYC Inc Webstore Purchase_;&nbsp; 

 

&nbsp;- **Android mobile phone x 1:** _1 x 500 = 500_; 

&nbsp;- **Desktop PC x 1:** _1 x 600 = 600_; 

&nbsp;- **USB Charger x 1:** _1 x 30 = 30_; 

&nbsp;

&nbsp;- **Total:** _1130_;")

 

2\Add a table control and set it's Items property to:

 

ForAll(MatchAll(TheHTMLText,"&nbsp;- \*\*[a-zA-Z0-9 ]*x \d:\*\* _\d+ x \d+ = \d+_").FullMatch,With({TheResult:Substitute(Substitute(Substitute(FullMatch,"&nbsp;-",""),"*",""),"_","")},{'Device Type':First(Split(TheResult,":")).Result,Qty:First(Split(Last(Split(TheResult,":")).Result," x ")).Result,'Qty x Unit Price':First(Split(Last(Split(TheResult,":")).Result," = ")).Result,'Total amount':Last(Split(Last(Split(TheResult,":")).Result," = ")).Result}))

 

vbofengmsft_0-1628058162134.png

In Power Automate:

vbofengmsft_1-1628058880530.png

vbofengmsft_2-1628058946721.png

vbofengmsft_3-1628058960035.png

Expression Reference:

1\

&nbsp;- \*\*[a-zA-Z0-9 ]*x \d:\*\* _\d+ x \d+ = \d+_

2\

replace(replace(replace(items('Apply_to_each')?['Match0'],'&nbsp;-',''),'*',''),'_','')

3\

first(split(outputs('Compose_2'),':'))

4\

first(split(last(split(outputs('Compose_2'),':')),' x '))

5\

first(split(last(split(outputs('Compose_2'),':')),' = '))

6\

last(split(last(split(outputs('Compose_2'),':')),' = '))

The Result:

vbofengmsft_4-1628059053603.png

Best Regards,

Bof

 

 

View solution in original post

Warren_Gibbs
Helper I
Helper I

Hi @v-bofeng-msft 

 

Thank you so much!   Two solutions 🙂

 

Best Regards

Warren

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

Hi @Warren_Gibbs :

First of all, I do not recommend that you use Power Automate to achieve this requirement.There two reasons:

1\Incur additional costs:We usually use regular expressions to parse strings, and using similar services in flow requires calling a third-party charging connector.

2\More complex

I will first provide a solution to this problem using Power APPS. If you don’t mind the above two points, then I can provide you with a solution based on power automate.

In Power Apps(Canvas):

1\Create a variable -TheHTMLText

 

Set(TheHTMLText,"- **Subject:**&nbsp; _Web Store Purchase Order_;&nbsp; 

&nbsp;- **Comments or special instructions:**&nbsp; _<p>Comments from Requester for consideration</p>_;&nbsp; 

&nbsp;- **Customer:**&nbsp; _XYZ Inc_;&nbsp; 

&nbsp;- **Service:**&nbsp; _XYC Inc Webstore Purchase_;&nbsp; 

 

&nbsp;- **Android mobile phone x 1:** _1 x 500 = 500_; 

&nbsp;- **Desktop PC x 1:** _1 x 600 = 600_; 

&nbsp;- **USB Charger x 1:** _1 x 30 = 30_; 

&nbsp;

&nbsp;- **Total:** _1130_;")

 

2\Add a table control and set it's Items property to:

 

ForAll(MatchAll(TheHTMLText,"&nbsp;- \*\*[a-zA-Z0-9 ]*x \d:\*\* _\d+ x \d+ = \d+_").FullMatch,With({TheResult:Substitute(Substitute(Substitute(FullMatch,"&nbsp;-",""),"*",""),"_","")},{'Device Type':First(Split(TheResult,":")).Result,Qty:First(Split(Last(Split(TheResult,":")).Result," x ")).Result,'Qty x Unit Price':First(Split(Last(Split(TheResult,":")).Result," = ")).Result,'Total amount':Last(Split(Last(Split(TheResult,":")).Result," = ")).Result}))

 

vbofengmsft_0-1628058162134.png

In Power Automate:

vbofengmsft_1-1628058880530.png

vbofengmsft_2-1628058946721.png

vbofengmsft_3-1628058960035.png

Expression Reference:

1\

&nbsp;- \*\*[a-zA-Z0-9 ]*x \d:\*\* _\d+ x \d+ = \d+_

2\

replace(replace(replace(items('Apply_to_each')?['Match0'],'&nbsp;-',''),'*',''),'_','')

3\

first(split(outputs('Compose_2'),':'))

4\

first(split(last(split(outputs('Compose_2'),':')),' x '))

5\

first(split(last(split(outputs('Compose_2'),':')),' = '))

6\

last(split(last(split(outputs('Compose_2'),':')),' = '))

The Result:

vbofengmsft_4-1628059053603.png

Best Regards,

Bof

 

 

View solution in original post

Warren_Gibbs
Helper I
Helper I

Hi @v-bofeng-msft 

 

Thank you so much!   Two solutions 🙂

 

Best Regards

Warren

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (3,426)