Need a flow to check for duplicates in Excel before adding a row

[robbrooks]

I'm very new to Power Automate and I'm sure there's a way to do what I need, I just don't have the skills yet. I'm hoping someone here can help me.

I have a Form that users can fill out to records hours. I have a simple flow built that collects the answers and plugs them into a table in Excel. Sometimes users incorrectly record their hours, so what I'm trying to build is a way that they can just enter the time again with the same form. I want the flow to see if the user name and date are already on the excel sheet in the same row, and if they are, I want it to update that line with the new time from the Form. If the user name and date are not on the form in the same row, then I want it to add a new row.

I don't know if it's best to have the flow check for this before it adds the item to Excel, or if it should add the row, and then have another step that would do the compare. Any help would be appreciated here!

[rzaneti]

Hi @robbrooks ,

Welcome to Power Automate 🙂

Yes, it is perfectly possible to achieve your needs with this flow! Before starting to work in a flow design for this specific task, I ask you: does your business rule allow repeated values in the 'UW' column? For example, can you have 2 entries from 'John Smith', but for different dates?

[robbrooks]

Hi @rzaneti and thanks! yes, the same user in UW can have multiple entries. Each would have a different date though.

[rzaneti]

Hi @robbrooks ,

I'm running some tests here. To we update your Excel table, it will be important to have a 'key column', which means a column where you will have only 'unique values'. It is like an 'id' for the records in your table. Without this 'unique values' column, any update via Power Automate can affect more than one record, resulting in incorrect entries in your table.

Based on your business rule, is it possible for you to include an additional column in the table, just to track these unique values? If not, there is a workaround, which is delete all records and insert them again in each flow run. I find this approach risky, because any error during the flow run may result in data loss.

Let me know which alternative fits better to your case!

[robbrooks]

Hey @rzaneti , Yes, I can add a field for unique identifiers, that's not a problem. Thanks for the help!

[rzaneti · Accepted solution]

Hi @robbrooks ,

Ok, we have a solution 🙂

Overview on the Excel table:

I will use the following table for this example. The only difference from yours is the 'Id' column, that I built dynamically, by using the formula =ROW()-1. It will guarantee that we have a autogenerated unique Id for each new record. 

Overview on the "form data":

To easily replicate the behavior of your MS Form, I created an object variable called 'form_response' with the same fields from your form. In your flow, you do not need to create this variable: just use your Form field directly in the further actions. 

Step 1 - List records from Excel Table:

Use the 'List rows present in a table' action to capture all records from your Excel table. Identify the file/table in the required fields. Also, click in 'Show advanced options' (below the fields) and other optional input will be displayed: make sure to set the 'DateTime Format' to ISO 8601 (highlighted in yellow). It will make sure that your Excel date matches to the Power Automate format: 

Step 2 - Filter records form Excel: 

In this step, you will check for records in the table that matches both to the UW and Date from the Form submission. 

Insert a 'Filter array' action and use the 'value' dynamic value from 'List rows present in a table' action as input. Then change the filter mode to advanced, by clicking in the button highlighted in yellow:

A text input box will appear. Enter the following expression: @and(equals(item()?['UW'], variables('form_response')['user']), equals(formatDateTime(item()?['Date'], 'yyyy-MM-dd'), formatDateTime(variables('form_response')['date'], 'yyyy-MM-dd'))):

Make sure to change the red text for the trigger output expression corresponding to the 'UW' answer in your form, and the green for the output expression corresponding to the 'Date' answer in your form.

Step 2.1. Getting your raw trigger outputs (needed to find your trigger output expressions)

These output expressions may be difficult when you start to use Power Automate (especially the outputs from MS Forms), so if you find any trouble in this step, please share the raw outputs from your Form submission and I help you to build the expressions.

Access flow details page

First go to your Power Automate home page, click in 'My flows' and click in the name of the flow that you want to access:

Access flow run page

You will see some details about your flow, and on the bottom of the page, a list with all flow runs. Click in one of the date/time:

Access details from your action

Then you will access the flow run page. The view is similar to the flow edit page, but here you are not allowed to change anything in the actions: you can only read the status of the flow, and even access the specific inputs/outputs from each individual action:

Raw outputs button

Go to your trigger (the first action) and click in it. It will expand the action view, and  display the "Show raw outputs" button in the OUTPUT section. In some cases, this button appears as 'Click to download':

Accessing the raw outputs

It will show a sidebar on the right side of your screen containing this JSON notation (if you had the 'click to download' option, it will open the outputs in a new tab):

Find the "body" property. It is always after the "headers" property. Make sure to remove any sensitive content from the image before sharing.

You also can find more details about how to access the outputs in Power Automate in this blog post that I wrote recently: http://digitalmill.net/2023/06/29/how-to-access-the-raw-outputs-in-power-automate/

Step 3 - Testing if there is an existing record

Now that we filtered the Excel table by UW and date, let's check if the filter returned any record. We use the length() expression for that (highlighted in yellow, which return the quantity of items from an array. Create a condition action and insert the following expression in the left side: length(body('Filter_array_2')). Make sure to replace the red text for the dynamic value from your 'Filter array' action, as its name may be different from mine.

Use the 'is greater than' operator (dropdown from the center) and pass the value 0 (highlighted in green) in the right side:

In this summary, we are asking Power Automate if our filtered table has more than zero items. If yes, we will update the record; if not, we will insert a new item.

Step 4 - Inserting a new item into the table:

In the "If no" block from your conditional, insert a 'Add a row into a table' action. After fill the details about the file and table, some new fields will be displayed, one per table column. Make sure to include the formula '=ROW()-1' in your 'Id' field (highlighted in yellow). For the other fields, pass the values from your MS Form answer dynamically:

Step 5 - Updating an existing record:

In the 'If yes' block, insert an 'Update row' action. The view and the fields are very similar to the 'insert' action, but now you will need to specify you key column (the column that contain only unique values) and the key value (the unique identifier of the record that you want to update). That's the reason why you need an 'Id' column, as mentioned in the previous post, and our formula solve it by inserting it automatically for the new records.

Choose 'Id' as key column (highlighted in yellow) and use the following expression to isolate the 'Id' of the filtered record from 'Filter array' action (highlighted in green): first(body('Filter_array_2'))['Id']. Make sure to replace the red text for the dynamic value from your 'Filter array' action, as its name may be different from mine.

As the only new data is the qty of hours, you can populate only this field with the dynamic value from your form answer. 

Output from new record

Let's run the flow with a new record from John Smith, at July 8th:

Output from existent record

Let's run the flow to update the existent record from John Smith, at July 7th, to 2.5 hours:

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

[robbrooks]

@rzaneti This looks amazing! Thank you! I have been away from the office, but I am going to work on setting this up today and tomorrow and testing it. Thank you!