Compare Excel table to SharePoint List than Create Items what are missing from SharePoint List

[SebS]

Hello,

This task is fairly easy if I use Apply to each + condition, but the challenge is that it takes really long time if deal with two lists what exceed 10k records. Is there a better and faster way to do it?

Like maybe Using Arrays or something.

I need to compare that two data sources every day and the item what will missing will be 1 to 10 max. To create them it's less than 1-minute compares with time to run Apply to each with condition to find them what takes 30-50 min at last for 10k records and waste so many calls by this.

any help appreciated.

[Chriddle]

In general it works like this:

1. Create an array of your current data
2. Add a select action for your new data with a new property "exists_in_current" with an expression like this: contains(current_data, item())
3. Add a filter action for your new data with filter "exists_in_current" eq False

An example

Compose (current data):

[
 {"name": "test1", "value": "value1"},
 {"name": "test2","value": "value2"},
 {"name": "test3","value": "value3"}
]

Compose 2 (new data):

[
 {"name": "test0", "value": "value0"},
 {"name": "test2","value": "value2"},
 {"name": "test3","value": "value3"}
]

Select:

From: 

outputs('Compose_2')

Map expression (switch map to text mode): 

addProperty(
	item(),
	'exists_in_current',
	contains(outputs('Compose'), item())
)

Filter array

From: body('Select')

item()['exists_in_current'] is equal to False

Output:

[
  {
    "name": "test0",
    "value": "value0",
    "exists_in_current": false
  }
]

[grantjenkins]

A few questions for you.

1. What fields are you comparing? You won't be able to compare object to object since you have ID, etc. in your SharePoint list that won't be in your Excel Table.
2. What about items that exist in both, but the data is slightly different - just one field for instance (will this ever happen, or just want to add rows that are missing in SharePoint)?
3. What about items that exist in SharePoint, but not in Excel? (Will this ever happen - say you delete a row from Excel).
4. How are you getting your data from Excel at the moment? And just confirming, it's an Excel Table, not a CSV.

[Chriddle]

> What fields are you comparing? You won't be able to compare object to object since you have ID, etc. in your SharePoint list that won't be in your Excel Table.

You can compare objects, see my example above. The map expression does this implicitly (contains function).

Of course, the structure of your objects must be the same for this.

[SebS]

@grantjenkins 

All good questions what I miss to explain.

1. You are right both objects are not the same not only by ID but in SharePoint there is more columns added. That's why I'm compare "Person Number" from Excel table to "Title" in SharePoint

2. I use batch Update to update all records what are existing in both Data Sources from below 

Batch Update SharePoint List With External Data - Power Platform Community (microsoft.com)

I was planning to use:

Power Automate Flow to batch create SharePoint List Items (tachytelic.net)

for creating missing data but I need to get my head around how to point right data fast to the batch Create function

3. If there is something what exist in SharePoint but not in excel I want to also find this Items and than change Status column from Active to Leaver and add today date to leaver date

4.It's an excel file I run create a table than using List rows present in table

My original Plan was to use already existing Match function in the batch update and try to Batch Create base on that but I can't get my head around how to put this to use 😞

[grantjenkins]

@Chriddle Yea, I meant we can't just use the raw objects - would need to extract out one or more relevant properties into objects that we can use for matching.

[SebS]

Here is the flow I use to Update

[SebS]

I could I think 

get Items from SharePoint

than initialize variable like varPersonNumber

Than in Apply to each append all Person number to an Array

then add another apply to each and inside add compose to extract only Title than create a condition where variable contain compose nothing and when it's false create an item, I think that should work.

[SebS]

The issue is that taking too long so I was looking to see if I can land with like compose of an array what I can use in batch create. or somehow use already generated data in batch update to isolate Person Numbers what I need to create in SP

[Chriddle]

Don't use loops for that. Creating an array of Person numbers is only one select action with  a Map expression something like: item()['Person_Number'].

Checking if this contains the title is also a select followed by a filter (see my flow description above)

[grantjenkins · Accepted solution]

Here's an example that would quickly find and create the missing items. Note that I've used List rows present in a table as assuming you are already using this. I'm assuming this would take quite a while with 10,000+ rows. If you were open to using Office scripts, then you could extract the JSON from the Excel file within seconds.

For this example, I'm using the following Excel Table.

And the following SharePoint List that is missing the last 4 items.

The full flow is below. I'll go into each of the actions.

List rows present in a table and Get items return all items from both Excel and SharePoint. I'm assuming you've set Pagination On and a suitable Threshold. As mentioned, we can use an Office Script to extract the rows of data as JSON a lot quicker than using List rows present in a table if you're interested.

Select extracts out the Title values from our SharePoint list into a simple array. Note that for the Map I've used Text mode (see arrow on screenshot).

Filter array takes in the data from the Excel Table and checks to see if the array of Titles do not contain the Person Number. This will result in an array of rows that don't currently exist in our SharePoint List.

You mentioned that there would likely only be approximately 10 items to add each time, so Ok to just have an Apply to each here since there would only be approximately 10 items in the Filter array.

And within the Apply to each, we use Create item using the following expressions to get the relevant data from Excel.

items('Apply_to_each')?['Person Number']
items('Apply_to_each')?['Amount']

After running the flow, our SharePoint List would contain the missing items.

[SebS]

@grantjenkins @Chriddle  Thank You to both of You.

@grantjenkins  Your solution is working perfectly, and it takes only 2.5 min for 6156 records so for 10K will not take much longer. 

I would like to hear about the Script approach to pull a JSON just keep in mind :

1. The Excel is store in SP Library

2. Excel file is created there from Attachment of an email and originally do not have a table

3. Table is created by Power Automate

[grantjenkins]

If you wanted to extend the flow so it also handled items that existing in SharePoint but not Excel, then you could do the following.

Note that I'd suggest running the script on some test data if you can first to ensure it works as expected.

For this example, I'm using the same Excel Table and data as before.

I've updated my SharePoint list to include a Status column (choice) and a Leaver Date column (date) for the Leaver. I set the Status column with Active as default value, and added a row XYC123 that doesn't exist in Excel.

See full flow below. I'll go into each of the actions.

Note that the top actions and the left parallel branch are exactly the same as before. I've just added another parallel branch (on the right) that handles items that exist in SharePoint, but not in Excel.

Select Leave uses the output from List rows present in a table and maps the Person Number.

Filter array Leave uses the output from Get items and filters on the array of Person Numbers do not contain Title. This will return all the items in our SharePoint list that don't exist in Excel.

Apply to each Leave iterates through each of the items in the Filter array Leave, and uses an Update item to change the Status to Leave and sets Leaver Date to the current date. The expressions used are:

items('Apply_to_each_Leave')?['ID']
items('Apply_to_each_Leave')?['Title']
utcNow()

If it's not taking too long to get the Excel data using List rows present in a table, then it might not be worth using an Office Script in this instance. On caveat with using Office Scripts are that they are saved in your OneDrive account so not ideal for flows that are used by multiple people (need to share permission to it), and a risk that if the person that has the Office Script in their OneDrive leaves the company, any flows that are using it may start to fail.

If you are interested though then let me know, and I can show you how to get your Excel data to JSON.

[SebS]

@grantjenkins 

That's perfect. So now Batch Update Handling Update off all that Items and the flows what You suggested handling the rest. With re use of Get and List row action from batch Update all the flow for 6200 Items completed in just 22 min which is really good.

Thank You 

[grantjenkins]

Just curious - how many items are getting updated each time you run the flow? And what fields are you checking to see if the items require an update?

[SebS]

@grantjenkins 

Everything is Updated in the Oracle database in a third-party system. Due to GDPR and Security, there is no way live access can be granted to Oracle DB. Also, access like this will require a premium connector, which will link the app I am creating to this Premium app, which will significantly increase the cost of the app. The App is used by around 250  to 400 users a month, and that's a lot of licenses for a premium app 🙂

So due to the above, I still can schedule daily Excel reports from Oracle DB, which are sent to an Email in excel form, and that's enough to keep the app up to date with all the changes in Employeed DB, which contains around 11k records 🙂

Now, suppose it comes to Batch Update. In that case, it's really cool as it works like copy and paste in excel 🙂  1000 records per batch means I don't care if the record change data in that instant of the update; I update all records anyway as it takes only 7 to 12 minutes to update using this approuch whole 11k items in Sharepoint also the biggest bonus is it cost you spend only 7 calls and not few thousand if you will check the individual record and if something changed you will update. The creation of an item is triggered only when the organization employ someone, and the status change action to leaver when they dismiss someone, so I do not need any Batch approach, which means the solution You helped me to build is efficient enough and even if it will cost me 100 calls the daily limit per user is 40K so I can leave with this, but If I deal with an Update in normal way this will limit my daily calls, and this is not one flow I process what manage a lot of records, so I need to plan and save calls.

[grantjenkins]

@SebS Makes sense.

I did add another parallel branch to the flow just for fun to handle updates. It checks to see what rows are different between Excel and SharePoint (looks at a subset of the columns I want to check) and only updates those records. So, if we had 11,000 rows and only 10 of those had changed then the new addition to the flow would only incur an additional 25 calls (approx.) and would complete in maybe a couple of minutes.

However, if it's already working well with the batch update then I wouldn't bother modifying it now.

[grantjenkins]

@SebS Makes sense.

I did add another parallel branch to the flow just for fun to handle updates. It checks to see what rows are different between Excel and SharePoint (looks at a subset of the columns I want to check) and only updates those records. So, if we had 11,000 rows and only 10 of those had changed then the new addition to the flow would only incur an additional 25 calls (approx.) and would complete in maybe a couple of minutes.

[grantjenkins]

@SebS One update to the Leave part of the flow. If an item in SharePoint doesn't exist in Excel, it will set the Status to Leaver and a Leave Date. At the moment it will still update the items that have already been set with a Status of Leaver. The below change will ignore those items as we have already set their Status.

I'd suggest changing the following:

With the following expression. Note that you'll need to go into Advanced mode and paste the expression in.

@and(
    not(equals(item()?['Status/Value'], 'Leaver')),
    contains(body('Select_Update'), item()?['Title'])
)

[SebS]

@grantjenkins 

You are absolutely right. I was so excited about getting this all to work that I forgot about this 🙂

Again Thank You.