Power Community

Power Community

Extract data from html table in email body

Use Case

I have found that there are many posts related to extract data from html table in the email body. This is the common scenario when organization tries to automate the process to increase personal productivity of users. So, I thought of sharing a solution to accomplish this task.

Scenario

Let’s take an example of getting information from partners. The users send a template to business partners in the form of html table to respond. Partners replied on the mail with the required information. Using Power Automate cloud flow, we will extract the data or required information from the business and stores those values in the excel sheet saved in SharePoint document library.

Input Source (Email)

ManishSolanki_2-1696763315968.png

We will extract the values for Price/PC, Currency, MOQ & Lead-time (weeks) from the email received from business partners.

Target Excel File

The target master excel file sheet has been saved in SharePoint document library. The excel sheet contains a table which has unique column ‘UniqueID’. Based on the unique column, values extracted from email body will be updated in the excel file.

ManishSolanki_1-1696763020797.png

Solution

There are many solutions to extract the data from email like convert html to text and parsing the string, AI builder etc. But here, I will make use of fx expressions to achieve this.

1. Start by creating a new Automated cloud flow.

ManishSolanki_3-1696763546722.png

2. Enter the flow name & select the trigger “When an email arrives (V3)”. Press Create button to proceed.

ManishSolanki_4-1696763667160.png

3. Expand trigger action by right click it. When an email arrives provides various filter options to choose from and accordingly the flow will trigger. You can filter based on sender, recipient(s), subject, with or without attachment(s) etc. Here, we will filter the incoming email on subject “RQF Automated” so that the flow will be triggered on a specific email.

ManishSolanki_5-1696763758234.png

4. Add Compose action and write an expression to extract html table from the email body

ManishSolanki_6-1696763874526.png

concat('')),'
')

5. Run the flow once to get to know which tags to target for extracting the data. Depends on the column’s formatting, html tags may vary so it recommended to know beforehand. Analyze the output of compose action and note the tags that contains header of data values. 

Item Code

Supplier

QTY

UniqueID

Price/PC

Currency

MOQ

Lead-time (weeks )

ABCDEF

XYZ

1000

ABCDEFXYZ

1

USD

10

2

EFGH

XYZ

2000

EFGHXYZ

2

USD

20

4

HIGL

XYZ

1000

HIGLXYZ

3

USD

20

6

MNOP

XYZ

2000

MNOPXYZ

4

USD

40

8

QRST

ZMR

5000

QRSTZMR

5

USD

50

10

UVWZ

ZMR

5000

UVWZZMR

6

USD

60

15

Here, we will target the rows with data columns and find the route or path (table > tbody > tr > td > p). Using this path in XPATH function, we will retrieve the data values.

6. Add another compose action, to get the values from html table using expression. In the expression, we will convert the string to xml & then apply XPATH to get values. We also convert into rows and each rows contains corresponding all column values using chunk function.

ManishSolanki_7-1696764142848.png

chunk(xpath(xml(outputs('Compose')),'//table//tbody//tr//td//p//text()'),8)
Pls note that as we have total 8 columns in the html table so we will use 8 in chunk function. The chunk function creates element for each row (including row) and each row contains an array of 8 elements (columns)

7. Next, add “Apply to each” action to iterate each row. As output array of compose 2 also contains header row, so we will use expression to start iterating from second element & skips the header row:

ManishSolanki_0-1696764363440.png

skip(outputs('Compose_2'),1)

Add “Update a row” action inside apply to each block, to update the row in excel based on the unique column.

ManishSolanki_1-1696764495287.png

Expression used for each column:

Column Name

Expression

Key Value
item()?[3]
Price/PC
item()?[4]
Currency
item()?[5]
MOQ
item()?[6]
Lead-time (weeks )
item()?[7]

As index starts from zero (0), so to get the value of first column (Item Code) you need to use item()?[0]. You could get the value of each row in the iteration using the indexer.

Output

When an email arrives with subject “RFQ Automated”, the flow will trigger. It extracts the values from table present in the email body and update the data in the excel file

ManishSolanki_2-1696764809010.png

Conclusion

So, we can now say that using fx expression we can automate the process by extracting the data from the table in the email body. This is the power of expressions in power platform. Using same concept, we could extract the data from any source as we could easily transforms JSON object to XML using expression and further apply filter using XAPTH as per the business requirement.

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Preferred Solution | New feature | Microsoft Dataverse

Click on the below image to navigate to my YouTube Channel. Please like and share your valuable feedback on this...

More Articles Like This

- Advertisement -spot_img