Power Platform Community Forum Thread Details

@grantjenkins

I wanted to see if someone could share some of their valuable knowledge on an issue I'm having.

What am I trying to Archive?

Structure a detailed power automate flow that would allow me to be able to compare a column called "Unique ID" in an Excel file to a different column called "UniqueExcelID" in SharePoint and identify the column values that match between the Excel and the SharePoint and only the IDs that match in the excel file under the column "Status (excel)" has a value of "Complete", have the flow take the IDs values that match and update the SharePoint using the multiple IDs values that match between the excel and the SharePoint. Have this flow run for only the ID value that matches, ignoring the nulls or other ID values that do not match. On the second run, the flow should only update the new matching IDs, not the already updated marching IDs.

What's the issue:

Flow keeps telling me that the column "Unique ID" in the Excel file does not match the "UniqueExcelID" column in SharePoint. Yet, when I do a Select, I see matching values in the column "Unique ID" in the Excel file and the "UniqueExcelID" column in SharePoint.

Not sure what I'm doing wrong.

Below are the 3 points that I'm trying to achieve.

Pull all Items from the Excel file that have the status "Complete" where the"Unique ID" from Excel matches the Sharepoint "UniqueExcelID" column.
Once the flow identifies those ID values, I want the flow to update SharePoint for those ID values that match between the Excel and SharePoint, but only for those values that on the Excel have a status of "Completed."
On the second and ongoing run of the flow, I want the flow to only update the ID values with the completed status that are new and not the previous IDs that were updated (I'm trying to build it in a way that it just ignores those already updated ID values so that the run time would decrease).

Below are the steps that I'm performing but getting stuck on step 5.

1. Trigger the flow:
- Choose the appropriate trigger action, such as "When a file is created or modified (properties only)" for SharePoint or "When a new file is added (properties only)" for OneDrive/Excel.

2. Initialize variables:
- Add the following variables:
- `ExcelIDs`: Array to store Unique ID values from Excel.
- `MatchingIDs`: Array to store matching UniqueExcelID values from SharePoint.
- `MatchingCompleteIDs`: Array to store matching UniqueExcelID values with Status = "Complete".

3. Get Excel file data:
- Use the "Get file content" action to retrieve the Excel file data.
- Use the "List rows present in a table" action to extract the data from a specific sheet and table in the Excel file.
- Specify the Excel file location or ID in the appropriate field.
- Specify the sheet name and table name where the Unique ID and Status columns are located.
- Add a "Filter query" to retrieve rows where Status = "Complete": `Status eq 'Complete'`.
- Add a "Select" action to extract only the "Unique ID" column values and save them into the `ExcelIDs` variable.
- Select the appropriate mapping for the columns.

4. Get SharePoint list items:
- Use the "Get items" action to retrieve the items from the SharePoint list.
- Specify the SharePoint site and list name in the appropriate fields.
- Add a "Select" action to extract only the "UniqueExcelID" column values and save them into the `MatchingIDs` variable.
- Select the appropriate mapping for the columns.

5. Compare and update SharePoint:
- Use the "Apply to each" loop action on the `ExcelIDs` array.
- Within the loop, add a "Condition" action to check if the current ID exists in the `MatchingIDs` array. Use the expression `contains(variables('MatchingIDs'), item())`.
- If the ID exists, add another "Condition" action inside the "If true" branch to check if the current ID also exists in the `MatchingCompleteIDs` array. Use the expression `contains(variables('MatchingCompleteIDs'), item())`.
- If the ID exists in both arrays, this means it has already been updated, so skip further actions.
- If the ID exists in the `MatchingIDs` array but not in the `MatchingCompleteIDs` array, this means it's a new match. Proceed with updating the SharePoint item.
- Use the "Update item" action to update the corresponding SharePoint item.
- Specify the SharePoint site and list name in the appropriate fields.
- Add a filter condition to match the UniqueExcelID column with the current ID: `UniqueExcelID eq '<currentID>'`.
- Specify the column(s) you want to update, e.g., set the Status column to "Complete" if required.
- Select the appropriate mapping for the columns.

6. Update variables and finalize the flow:
- Add a "Append to array variable" action to update the `MatchingIDs` array with the new matching IDs found in the current run. Use the expression `concat(variables('MatchingIDs'), item())`.
- Add a "Append to array variable" action to update the `MatchingCompleteIDs` array with the new matching IDs found in thecurrent run, but only if their Status is "Complete" or any other specific condition. Use the expression `if(equals(item()?['Status'],'Complete'),concat(variables('MatchingCompleteIDs'), item()?['UniqueExcelID']),variables('MatchingCompleteIDs'))`.
- Save the flow.