YouTube Shorts Demonstration Video
How to get duplicate values from any datasource, array, or JSON array in Power Automate.
Select Action Note:
Add a Select action if you want to get more than just exact duplicates. Use this action to select specific item columns to check for duplicates.
Filter Array Action Note:
The expression only uses strings & returns the index of the 2nd argument item if it shows up the number of occurrences specified in the 3rd argument, otherwise it returns -1 Expression in the Filter Array: nthindexof(string(body(‘Select’)), string(item()), 2)
This template flow is already set up with an example Union( ) expression and Apply to each loop for those who want to delete the duplicates from their dataset.
Copy this scope code and Control + V paste it into the "My clipboard" tab of a new action menu:
{"id":"5e530dcd-5589-4e51-b541-d5df4b5e7765","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-29fbd1aa-441a-4ca4-badb-fd92d9ce165f"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Find_and_delete_duplicates","operationDefinition":{"type":"Scope","actions":{"List_rows_present_in_a_table":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"me","drive":"b!CVcYUvLh8EGkvkCsqwCTp7Pqs2dO4IxIl2FLeQnvIk-zJXFeRmKbRIbbO8P0Lkhi","file":"01PCEUDVBD2GUIKLQ7CRFL32GZXK5EIHSQ","table":"{0DF9A07A-9DC2-436B-85CC-A958741ED454}"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"description":"You can change this out with any type of get data action for any type of datasource, just update the From field in the following Select action with the outputs of the new datasource.","metadata":{"01PCEUDVBD2GUIKLQ7CRFL32GZXK5EIHSQ":"/Book.xlsx","operationMetadataId":"f5de7156-8cde-47e0-949f-5bd37bea2f64","tableId":"{0DF9A07A-9DC2-436B-85CC-A958741ED454}"}},"Select":{"type":"Select","inputs":{"from":"@outputs('List_rows_present_in_a_table')?['body/value']","select":{"Col1":"@item()?['Col1']","Col2":"@item()?['Col2']"}},"runAfter":{"List_rows_present_in_a_table":["Succeeded"]},"description":"Add a Select action if you want to get more than just exact duplicates. Use this action to select specific item columns to check for duplicates.","metadata":{"operationMetadataId":"218007cf-3241-4ebf-906c-563b82e36850"}},"Filter_array":{"type":"Query","inputs":{"from":"@body('Select')","where":"@greater(nthIndexOf(string(body('Select')), string(item()), 2), -1)"},"runAfter":{"Select":["Succeeded"]},"description":"nthIndexOf(string(body('Select')), string(item()), 2) ||| Only uses strings & returns the index of the 2nd argument item if it shows up the number of occurrences specified in the 3rd argument, otherwise it returns -1","metadata":{"operationMetadataId":"04080a21-d0d1-4018-9d1b-cb6411877800"}},"Apply_to_each":{"type":"Foreach","foreach":"@union(body('Filter_array'), body('Filter_array'))","actions":{"Delete_a_row":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_excelonlinebusiness","operationId":"DeleteItem","apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"parameters":{"source":"me","drive":"b!CVcYUvLh8EGkvkCsqwCTp7Pqs2dO4IxIl2FLeQnvIk-zJXFeRmKbRIbbO8P0Lkhi","file":"01PCEUDVBD2GUIKLQ7CRFL32GZXK5EIHSQ","table":"{0DF9A07A-9DC2-436B-85CC-A958741ED454}","idColumn":"Col1","id":"@items('Apply_to_each')?['Col1']"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"metadata":{"01PCEUDVBD2GUIKLQ7CRFL32GZXK5EIHSQ":"/Book.xlsx","operationMetadataId":"92987169-4d74-4aba-8c99-20877b3599de","tableId":"{0DF9A07A-9DC2-436B-85CC-A958741ED454}"}}},"runAfter":{"Filter_array":["Succeeded"]},"description":"The union expression helps remove the extra duplicate value so it only deletes 1 of the duplicate rows. If you need to reference something in the loop array values, use items('Apply_to_each')?['InsertColumnNameHere']}","metadata":{"operationMetadataId":"325d06d0-d08b-4c8e-9344-d06b5a4c6acc"}}},"runAfter":{},"metadata":{"operationMetadataId":"0c3ee8e3-6aec-4537-988e-f8849d28bca6"}}}
