Split an Excel table to separate Excel files

(0) Share

Report

Posted on by Rapier83

Dear All,

I should solve the following task with Power Automate:

There is a table with 3 columns (Source_table and SourceName, Customer, Values columns) in a Sharepoint document folder ("Source_folder"). The first column is SourceName, and I should split this table based on the value of this column. Rows with same Source name should split to separate Excel files which are named based on the Source column group (Source1.xlsx, Source2.xlsx and Source3.xlsx). These new files should be created in a different folder (Destination_folder) on same Sharepoint site.

Source_table looks like this:

Destination files and contents:

Further details:

- When a new group will be added to Source_table (e.g. Source4), it should create a new file (Source4.xlsx) during the next run.

- Every time when we re-run the flow it should create new destination files (for all the groups).

- If a destination file exists (e.g. Source1.xlsx), it should be overwritten

Is it possible use case to solve in Power Automate?

Thank you for your help!

Source_table exam...

Destination_table...

Your file is currently under scan for potential threats. Please wait while we review it for any viruses or malicious content.

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

David_MA 13,241 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!
You would need to do something like this:

When the flow runs, get all of the values from the SourceName column. This will contain duplicates.

Use a union() expression on the values to get unique values and put them into an array variable.

This will show you how to do this: How to Get Unique Values from an Array in Power Automate - Power Tech Tips

Then use an apply to each on the values in the array variable.

In the apply to each, use the Excel get rows action with a filter query to get the rows based on the current value in the apply to each.

Then create the create the spreadsheet and add the rows from the get rows action with the filter query.

This is a good tutorial on how to create the spreadsheet and add the rows. https://youtu.be/RB_ySjhm9Sg?si=L6XLrZZtD-0Pn-Gt

Was this reply helpful? Yes No
Rapier83 on at

Like (0)

Report
Copy link

Link copied!

Hi David,

Thanks for your reply. Based on this and some instructions from Copilot I created the following flow:

I try to show the details:

Union is union(body('Select'),body('Select'))

For file content I created an example Excel file, with empty table which has same columns as the Source table, from where I would like to copy the data. Then I get the content with a separate flow and copy here the results. I used this flow:

In the last step, I try to add filtered data (Current item in Row filed are from the Apply to each 2) to these newly created excel files.

When I run this flow, it starts, but after 10 minutes it drop a time-out message... The source table contains 100k+ rows. I don't know that the number of the rows is the issue, or I missed something in my flow.
Can you help me with some instructions?

Was this reply helpful? Yes No