Remove duplicates in Excel Table or Delete all rows in Excel table.

[Alan-Hossack]

Hi,

I have a flow set up to combine the data from tables in 6 separate excel workbooks into one 'master' table in another workbook.

I have no issues getting the data in or adding it to the table, but I need to do one of the following to the 'master' table:

1. Remove all rows/data from table (at the start of Flow); or
2. Remove all duplicates from table (at the end of Flow)

Has anyone done something similar or have some pointers on what & how I could accomplish this?

Thanks!

- Alan

[v-yamao-msft · Accepted solution]

Hi @Alan-Hossack ,

Remove all rows/data from table at the start of Flow could be easily achieved using the following configuration.

Please check the image for a reference.

Note: If you have more than 256 rows, you need to set Top Count to its maximum value.

Best regards,

Mabel          

[Anonymous]

Hi

this doesnt work for me, i still get an error cannot find id

---

@v-yamao-msft wrote:

Hi @Alan-Hossack ,

Remove all rows/data from table at the start of Flow could be easily achieved using the following configuration.

Please check the image for a reference.

Note: If you have more than 256 rows, you need to set Top Count to its maximum value.

Best regards,

Mabel          

---

[Alan-Hossack]

One problem Iran into was that the Flow would error if the table had a filter applied to it. Clearing the filter fixed the issue though.

[Anonymous]

thanks for that, i checked and it was so i cleared the filters but i am still getting the same message:

"statusCode": 404,

"message": "Resource not found"

[Anonymous]

I found this to be SUPER confusing, but here is the pain I went through, hopefully it will help someone else.  I noticed in the "Apply to Each" step, there is a field where you select the output from the previous operation.  There are 2 options (I have no idea why):  List of Items (List of Items), and value (List of Items).  When I pick the first one, it fails with the error:  The execution of template action 'Apply_to_each_2' failed: the result of the evaluation of 'foreach' expression '@body('List_rows_present_in_a_table')' is of type 'Object'. The result must be a valid array.

However, when I pick value (List of Items), it works.  OK Microsoft, why do you put something in there that doesn't work?

[AlexEncodian]

@Alan-Hossack 

Encodian's Utility - Array Remove Duplicates action will automatically delete duplicates in a JSON array. As a utility action, it consumes only 0.05 Encodian credits per operation and is therefore designed to be low cost at high volume. 

Please see the example solution here: Remove duplicate entries from Excel files in Power Automate