Extract information in Excel using Named Entity Recognition(NER) and Power Automate

In this tutorial, you'll create a Power Automate flow to extract text in an Excel spreadsheet without having to write code.

This flow will take a spreadsheet of issues reported about an apartment complex, and classify them into two categories: plumbing and other. It will also extract the names and phone numbers of the tenants who sent them. Lastly, the flow will append this information to the Excel sheet.

In this tutorial, you'll learn how to:

  • Use Power Automate to create a flow
  • Upload Excel data from OneDrive for Business
  • Extract text from Excel, and send it for Named Entity Recognition(NER)
  • Use the information from the API to update an Excel sheet.

Prerequisites

  • A Microsoft Azure account. Create a free account or sign in.
  • A Language resource. If you don't have one, you can create one in the Azure portal and use the free tier to complete this tutorial.
  • The key and endpoint that was generated for you during sign-up.
  • A spreadsheet containing tenant issues. Example data is provided on GitHub
  • Microsoft 365, with OneDrive for business.

Add the Excel file to OneDrive for Business

Download the example Excel file from GitHub. This file must be stored in your OneDrive for Business account.

Examples from the Excel file

The issues are reported in raw text. We will use the NER feature to extract the person name and phone number. Then the flow will look for the word "plumbing" in the description to categorize the issues.

Create a new Power Automate workflow

Go to the Power Automate site, and login. Then click Create and Scheduled flow.

The workflow creation screen

On the Build a scheduled cloud flow page, initialize your flow with the following fields:

Field Value
Flow name Scheduled Review or another name.
Starting Enter the current date and time.
Repeat every 1 hour

Add variables to the flow

Create variables representing the information that will be added to the Excel file. Click New Step and search for Initialize variable. Do this four times, to create four variables.

The step for initializing variables

Add the following information to the variables you created. They represent the columns of the Excel file. If any variables are collapsed, you can click on them to expand them.

Action Name Type Value
Initialize variable var_person String Person
Initialize variable 2 var_phone String Phone Number
Initialize variable 3 var_plumbing String plumbing
Initialize variable 4 var_other String other

information contained in the flow variables

Read the excel file

Click New Step and type Excel, then select List rows present in a table from the list of actions.

Add excel rows into the flow

Add the Excel file to the flow by filling in the fields in this action. This tutorial requires the file to have been uploaded to OneDrive for Business.

Fill the excel rows in the flow

Click New Step and add an Apply to each action.

Add an apply to each action

Click on Select an output from previous step. In the Dynamic content box that appears, select value.

select output from the excel file

Send a request for entity recognition

If you haven't already, you need to create a Language resource in the Azure portal.

Create a Language service connection

In the Apply to each, click Add an action. Go to your Language resource's key and endpoint page in the Azure portal, and get the key and endpoint for your Language resource.

In your flow, enter the following information to create a new Language connection.

Note

If you already have created a Language connection and want to change your connection details, Click on the ellipsis on the top right corner, and click + Add new connection.

Field Value
Connection Name A name for the connection to your Language resource. For example, TAforPowerAutomate.
Account key The key for your Language resource.
Site URL The endpoint for your Language resource.

Add Language resource credentials to the flow

Extract the excel content

After the connection is created, search for Text Analytics and select Named Entity Recognition. This will extract information from the description column of the issue.

Extract the entities from the Excel sheet

Click in the Text field and select Description from the Dynamic content windows that appears. Enter en for Language, and a unique name as the document ID (you might need to click Show advanced options).

Get the description column text from the Excel sheet

Within the Apply to each, click Add an action and create another Apply to each action. Click inside the text box and select documents in the Dynamic Content window that appears.

Create another apply to each action.

Extract the person name

Next, we will find the person entity type in the NER output. Within the Apply to each 2, click Add an action, and create another Apply to each action. Click inside the text box and select Entities in the Dynamic Content window that appears.

Find the person entity in the NER output

Within the newly created Apply to each 3 action, click Add an action, and add a Condition control.

Add a condition control to the Apply to each 3 action

In the Condition window, click on the first text box. In the Dynamic content window, search for Category and select it.

Add the category to the control condition

Make sure the second box is set to is equal to. Then select the third box, and search for var_person in the Dynamic content window.

Add the person variable

In the If yes condition, type in Excel then select Update a Row.

Update the yes condition

Enter the Excel information, and update the Key Column, Key Value and PersonName fields. This will append the name detected by the API to the Excel sheet.

Add the excel information

Get the phone number

Minimize the Apply to each 3 action by clicking on the name. Then add another Apply to each action to Apply to each 2, like before. it will be named Apply to each 4. Select the text box, and add entities as the output for this action.

Add the entities from the NER output to another apply to each action.

Within Apply to each 4, add a Condition control. It will be named Condition 2. In the first text box, search for, and add categories from the Dynamic content window. Be sure the center box is set to is equal to. Then, in the right text box, enter var_phone.

Add a second condition control

In the If yes condition, add an Update a row action. Then enter the information like we did above, for the phone numbers column of the Excel sheet. This will append the phone number detected by the API to the Excel sheet.

Add the excel information to the second if yes condition

Get the plumbing issues

Minimize Apply to each 4 by clicking on the name. Then create another Apply to each in the parent action. Select the text box, and add Entities as the output for this action from the Dynamic content window.

Create another apply to each action

Next, the flow will check if the issue description from the Excel table row contains the word "plumbing". If yes, it will add "plumbing" in the IssueType column. If not, we will enter "other."

Inside the Apply to each 4 action, add a Condition Control. It will be named Condition 3. In the first text box, search for, and add Description from the Excel file, using the Dynamic content window. Be sure the center box says contains. Then, in the right text box, find and select var_plumbing.

Create a new condition control

In the If yes condition, click Add an action, and select Update a row. Then enter the information like before. In the IssueType column, select var_plumbing. This will apply a "plumbing" label to the row.

In the If no condition, click Add an action, and select Update a row. Then enter the information like before. In the IssueType column, select var_other. This will apply an "other" label to the row.

Add information to both conditions

Test the workflow

In the top-right corner of the screen, click Save, then Test. Under Test Flow, select manually. Then click Test, and Run flow.

The Excel file will get updated in your OneDrive account. It will look like the below.

Test the workflow and view the output

Next steps