Is it possible to merge three Excel sheets into one using Microsoft Power Automate?

Question

I am experimenting with Microsoft Power Automate but quite recently I encountered a problem which is too complex for me to solve.

I have three Excel sheets in three separate folders on my SharePoint site. Each of the tables contains relevant data. I would like to merge those three sheets into one sheet using Microsoft Power Automate.

Below I attach the screenshots of what I was trying to do to solve the problem:

1.Here is how the folders with the Excel data look in SharePoint initially:

2. Here is an example of how the data looks in a specific folder:

3. Here is an example of what the data from that folder looks in the Excel:

4.Below I will present what the data looks like in Power Automate and what flow I have built in order to try and solve the issue:

5. Next I would like to show you how the specific elements of my flow look in detail:

On the picture above you can see that I am launching the flow using a button in another list. The button works correctly and executes the flow as I wanted it to do.

6.After all those steps I would finally like to show how the output from my flow looks in my Excel file.

What I would like to achieve to load the data from the three aforementioned Excel files, insert it into one Excel worksheet so that the data is grouped in chronological order.This means that basically we are first loading the data from one Excel worksheet and inserting it into the Excel sheet "Data1" and later on loading and inserting the two remaining sheets.

Taking into account in each file there are three rows of data, altogether there should be nine rows of data inserted into my Excel File "Data1".

If you have any other idea how to solve the problem, that is more than welcome. You do not have to rely on my data and instead you could use some data of your own.

I know that the issue is quite complex and long however I hope that one of you might know how to help me.

Many thanks!

Answer 1

Ok, so your workflow works, but you would like to avoid to copy empty rows/faulty rows.

To handle this you can insert a Condition in every loop actions and check if the Date or KPI are empty like this:

This way you skip empty rows, and only update the final table with relevant rows :)