Unanswered

Remove duplicate values from one Excel column and copy the list into a new Excel file.

(0) Share

Report

Posted on by Antje1

I would like to remove all duplicate values in column [Sales Order #] from Excel file A. After that, the entire list (all columns) needs to be copied into a new Excel file B. Unfortunately, I do not have access to Power Automate Desktop nor Premium features. Is this possible?

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Mitanshu 1,650 Moderator on at

Like (0)

Report
Re: Remove duplicate values from one Excel column and copy the list into a new Excel file.

1 - Use Excel Online (business) connector with Action: List rows present in a table

2 - Now read your original file using this - the output in body/value will be an array.

3 - Now add a compose action with formula:
union(outputs('List_rows_present_in_a_table')?['body/value'],outputs('List_rows_present_in_a_table')?['body/value'])
The union expression will give you distinct values from the array received in step 2.

4 - Use the Select action to pick your desired columns

5 - Write the output to your destination Excel file.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Antje1 21 on at

Like (0)

Report

Re: Remove duplicate values from one Excel column and copy the list into a new Excel file.

Thank you very much. I got this far:
What function do I use to write the output as an Excel table? I used "create table" but ran into an error. I also need to make sure that the same file can be overwritten each time the flow runs.

Was this reply helpful? Yes No
Mitanshu 1,650 Moderator on at

Like (0)

Report

Re: Remove duplicate values from one Excel column and copy the list into a new Excel file.

Can you confirm if based on my previous reply, you were able to get unique/distinct [Sales Order #] from the union expression?

The union expression will give you an array and you should loop through this array in your Apply to Each
Your select statement should contain all the columns that you are going to write in your destination file.

For your follow-up question:
Is your destination Excel file already created (with blank table) and available in SharePoint/OneDrive?

If yes, then you should add new rows to that Table in the destination Excel file so that each time the Flow runs, you are appending new rows to the same file.

In case you want to over-write (replace) instead of append, then you will first need to list all rows in your destination file, apply to each row of destination table, delete row. After that you can then write rows to your destination table based on output of union expression.

Creating a new table at every flow run will not give you a single destination table with combined results from all runs.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item.
If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Antje1 21 on at

Like (0)

Report

Re: Remove duplicate values from one Excel column and copy the list into a new Excel file.

Unfortunately, I am not able to get the flow running.
It looks like I am getting unique values, but I am running into an issue during "apply to each". The flow will time out after 10min.

Was this reply helpful? Yes No