Introduction

In this post let us learn how we can create items with lookup fields from the excel workbook in Power Automate. Also, please note this is very simple method of creating lookup items.

Scenario

Lets consider 2 lists. Area which is the lookup list with Title field. Branch is the child list with Title and Area field which is a lookup mapped to the Area list.

There are list of branches provided as a data in an excel workbook with the branch and area names. The excel is stored in Onedrive folder. Sample items is shown below. In order to retrieve the items from the excel workbook, make sure you convert the sheet into a table format.

Now the objective is to add all the items from the excel workbook in to the branch list with the lookup field mapped with the Area list. The output should be like the below

Let’s start building Power Automate Flow

For demo purpose, I have used a manual trigger. Let’s see the actions below.

Get Items – get lookup list items

This action is used to get all the lookup items and store it in the variable. Since we are going to map the Title and get the value of ID field, its enough to get only Title and ID from SharePoint.

Variable

The next action is to initialize the variable. Since we have the values, we can do the initialize and also set the values to the variable in the same action. Use the below expression to set the value. I gave the variable name as AreaItems

outputs('Get_items')?['body/value']

List rows present in a table

Using this action, we can retrieve the rows from the excel workbook which is stored inside a folder in my personal Onedrive. It is not required that the file should be in Onedrive, it can be in SharePoint library or other repository too. This action will return the selected table of items from the excel workbook.

Apply to each

Using this action we have to iterate through the items from the table. For each item we need to find the lookup id based on the value and pass the id and other item values to create an item in SharePoint list.

outputs('List_rows_present_in_a_table')?['body/value']

All the actions mentioned below should be inside the Apply to each action.

Filter array

Use this action to filter the Lookup items.

The input to this action should be the variable AreaItems which is variables(‘AreaItems’)

The value to be compared is the Title, so the expression in the first textbox should be item()?[‘Title’]

The operator must be is equal to

The value to be compared is the value from the excel workbook, so the expression should be items(‘Apply_to_each’)?[‘Area’]

Create Item

The final action is to add the item in SharePoint list. For the lookup field add custom item or choose dynamic expression and enter the below expression. The below expression just select the ID property of the filtered array’s first element.

body('Filter_array')?[0]?['ID']

Once all the required values are defined, try to execute the flow and it should work and you can see the items added in the list with the lookup values perfectly mapped.

Conclusion & What’s next?

Thats it, hope we had learned something new.

Please leave your comments and also let me know if you have any issues or any scenario that you want me to implement using Power Automate.

I hope you enjoy reading the post, see you soon in another post.

Cheers. Happy Flowing…