Few more questions:
1. In your run, how many iterations does the apply_to_each say (Show 1 of ??)? For example, below mine ran 32 times...
2. Why did you run the flow twice? The flow should only need to be run once as the List Rows-->Apply to Each runs through all rows of the spreadsheet...
OK, I understand now. The duplication is because you run the flow against the same data multiple times, I was assuming the data was getting duplicated on a single run.
So this is a bit more complex...you will need to determine if the row has been processed before or not. There are many approaches you can take but since this seems like a brand new process, this is the approach I would take.
Note: This solution assumes you aren't going to be "modifying" the data in Excel and expecting it to update your list (and if you do, that'll require different logic)
1. Create 2 new columns in EXCEL. One called ID and one called Processed (you can even have this hidden if you don't want people to see this). Leave Processed blank but have ID be soething like a counter, just so each row has a unique number.
2. In your List rows present in a table (call to excel), add in a Filter Query of
Processed eq ''
3. Now you'll only have those rows that haven't been added to your list. So you can use the same Create Item to add the SharePoint data.
4. WIthin the Apply_to_each loop, right after your Create Item step, update your Processed Row in excel to "yes". Use the ID as Key Column, and use the ID from "List rows in a table" call.
5. Once you add this in, all your fields in Excel will appear, just add YES to your Processsed column.
That should be it....hopefully that makes sense...
OK, so here is the approach for this scenario.
Setup
In your Excel file, keep the ID field that will have a unique value (a counter will do). We need something to be able to link records up between your Excel sheet and your SharePoint list. Here is my dummy data....I named my ID field DisciplineID in both the Excel file and in SharePoint.
Logic
The basic of the flow is to:
- Loop trhough every row in your excel file
- For the current row, check to see if the ID matches a record in SharePoint
- If a row matches, then update that record
- If not, then create a new record
Flow
1. Use List rows present in table from Excel (with no filter query if you put one in from my last suggestion).
2. Now use and Apply to each to loop through each row, and do a get items call against the SharePoint list. This uses a filter query to see if any rows have the same DisciplineID.
3. Now check to see if the Get Items returned any records.
Expression here is:
length(body('Get_items')?['value'])
4. If YES then you want to UPDATE the record.
Note: ID here is from the Apply_to_each_2 loop.
5. If NO then create a new record.
1. It does not appear you carried out the "Setup" section, where you NEED to create an ID to track this with (i.e., Discipline ID). Make SURE you add the new ID (DisciplineID) to both your SharePoint list and your Excel file.
Note: When creating this column in both SharePoint and Excel, do not name it ID (which is why I suggested using DisciplineID). That is because in SharePoint there already exists and ID field that you don't want to conflict with.
2. You Filter query is wrong. The filter query is a box that will only bring back the rows that this filter query is true for (so follow what I did in my instructions...it is saying bring back the rows from SharePoint where the DisciplineID is equal to the DisciplineID in Excel.
I fixed the setup section with the correct ID formatting.
The filter query looks similar to yours but it is still giving an error message.
Helpful resources
Power Automate News & Announcements
Keep up to date with current events and community announcements in the Power Automate community.
Community Calls Conversations
A great place where you can stay up to date with community calls and interact with the speakers.
