Skip to main content

Notifications

Power Automate - Building Flows
Answered

Struggling to convert an HTML table into an array

(0) ShareShare
ReportReport
Posted on by 6
Hello, I'm struggling to complete a flow that would convert email body table into an array of key-value.
 
I start off by capturing the email and converting HTML to Text. The email looks like this:

Dear customer, please see your orders below
Order # Purchase Date Return Date
AAA1 2/8/2020 5/8/2020
B21 2/8/2021 6/8/2021
C12 2/8/2022 3/8/2022
DDD1 2/8/2023 7/8/2023
EEF3 2/8/2024 8/8/2024

Please confirm.

First, I need to only keep data that is in the table. The only way I could think of is cutting out everything before the word "Order#" and after "Please", including the word. Is there a better way?

Then, by using delimiters, I convert the text into an array that looks like this: "Order #", "Purchase Date", "Return Date", "AAA1", "2/8/2020" and so on. How can I turn it into an array of key-value that would look like this:

[
  {
    "Order": "AAA1",
    "EffDate": "2/8/2020",
    "ExpDate": "5/8/2021"
  },
  {
    "Order": "B21",
    "EffDate": "2/8/2021",
    "ExpDate": "6/8/2021"
  }


and so on. Any help is appreciated!
 
  • Verified answer
    Chriddle Profile Picture
    Chriddle 7,300 on at
    Struggling to convert an HTML table into an array
    If your values don't contain spaces, you can do the following:
     
     
    From:
    chunk(
    	split(
    		replace(
    			replace(
    				body('Html_to_text'),
    				'Order # Purchase Date Return Date ',
    				''
    			),
    			decodeUriComponent('%0A'),
    			' '
    		),
    		' '
    	),
    	3
    )
     
    Map Order:
    item()[0]
    Map Purchase:
    item()[1]
    Map Return:
    item()[2]
     
     
  • Chriddle Profile Picture
    Chriddle 7,300 on at
    Struggling to convert an HTML table into an array
    To get the table, use somethig like the following expression with your email body (with outputs('Compose') as your email body).
    If there are multiple tables, use nthIndexOf.
    slice(
    	outputs('Compose'),
    	indexOf(
    		outputs('Compose'),
    		'<table'
    	),
    	add(
    		indexOf(
    			outputs('Compose'),
    			'</table>'
    		),
    		8
    	)
    )
     
    If the HTML table is XML compliant (or you can make it so, e.g. with some string replacements), you can use the following:
    (Assuming the HTML is in outputs('Compose'))
     
    From:
    skip(
    	xpath(
    		xml(outputs('Compose')),
    		'//tbody/tr'
    	),
    	1
    )
    Map Order:
    trim(
    	xpath(
    		item(),
    		'string(//td[1])'
    	)
    )
    Map Purchase:
    trim(
    	xpath(
    		item(),
    		'string(//td[2])'
    	)
    )
    Map Return:
    trim(
    	xpath(
    		item(),
    		'string(//td[3])'
    	)
    )
     
  • Suggested answer
    MrGott Profile Picture
    MrGott 101 on at
    Struggling to convert an HTML table into an array
    Another way could look like this:
     
    you could even go further and use the xpath function to get the values, but i think this is more intuitive
     
    PS: The first line mus be filtered out. Normaly HTML uses th tag for the table headers, but yours uses td with a strong tag.
  • bscarlavai33 Profile Picture
    bscarlavai33 513 on at
    Struggling to convert an HTML table into an array

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,422

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,711

Leaderboard