Answered

Uploading Items from Excel with existence check

(0) Share

Report

Posted on by CrimellaD

Hello,

I am fairly new to the Power Platform in general, I only have used for a 2/3 months now, but I am no complete stranger to programming and designing functions and processes, though it has never been my main occupation (so be kind if I have made some rookie mistake).

Now, I have built a Flow that is integrate into a PowerApp and does the following:

Take an item ID (contained in a Sharepoint List, that I will call "List1") and an Excel file (containing a Table with two columns: "Code", "Description") uploaded by the user from PowerApps
Copy the Excel file to a SharePoint Document Library
Retrieve the Table and its content from said Excel file

Then my goal is to verify that each item from the Excel file exists in a SharePoint List (lets call it "List2"), if not add it, then take its ID.

I then populate a Mapping Table linking the List1_ItemID and each List2_ItemID.

I currently do as follows:

For each Row in the Excel I do a Get Items action filtered on the Code value
If it is found I take its ID and create an item in the Mapping Table linking the two ID values, if not I create the Item in List2, then take its ID and make the relationship in the Mapping Table.

When everything is finished delete the Excel file an the Flow ends.

It all works well and does what it is supposed to do BUT it takes a lot of time: for an Excel file with 400 records it takes around 20min to complete.

I was wondering if I should arrange the flow differently (maybe there is a way to check items existence in a bunch instead of looping through an calling Get Items so many times) or some best practices I could implement to speed up things.

Thank you all in advance!!

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

creativeopinion

10,508 Moderator on at

Like (1)

Report

Copy link

Link copied!

@CrimellaD Using a Get Items action inside an Apply to Each action (which loops through each Row from your Excel Table) is inefficient. Best practice is to run the Get Items action once in a flow—outside of an Apply to Each action. Then use a Filter Array action to cross-reference the SP list items with your Excel value.

I run though how to cross-reference an Excel table with a SP list in this YT Tutorial: Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️ Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

Was this reply helpful? Yes No

ScottShearer 25,290 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

@CrimellaD

I agree with @creativeopinion but would like to add that you could also use an OData filter in your get rows action - it may be a little less complicated. Here is a link to a blog post that will get you started.

Was this reply helpful? Yes No
CrimellaD 10 on at

Like (0)

Report
Copy link

Link copied!

Sorry for the late reponse, I had some spare time to test the method and I must say I think it is the right way to go. Also a powerful tool to use, I needed some sort of logical indexing to be used in my flow and this method allows me to think about the problems from such prospective.

I will take the necessary time to properly learn and use it, but the first draft result I obtained is really promising!

Thanks

Was this reply helpful? Yes No