0

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: enter image description here

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

enter image description here

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

enter image description here

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: enter image description here

  1. Next I would like to show you how the specific elements of my flow look in detail: enter image description here

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.

enter image description here enter image description here enter image description here enter image description here

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

enter image description here

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!

2
  • 1
    Hi Mateusz, I quicly recreated your flow and it works wihtput issues. So what is your issue here? If you want to sort it by date, I think you should have the table in the Data spreadsheet do that. Otherwise you can put the input from the "List rows" action into an array and sort that on date. But the other way seems easier. If its the blank rows that are the issue I would say they are most likely due to having empty rows in the KPI spreadsheets.
    – RuneBH
    Jan 13 at 12:36
  • Hi actually you are right. It's my mistake I thought that the data is incorrect but because of the empty rows, but I had a second look and everything is actually ok, hilarious. Do you have any idea how to get rid of the empty rows though? Jan 13 at 13:40
2

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:

enter image description here

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

1
  • By the way, is it possible to add the path of the folder and the the name of the file in that folder, so that the data is on the same line? This would basically mean that apart from the data that I have in my two columns, folder path and the name of the file would be added to my output in Excel(so that all the data is on the same line)? e.g "Column1","Column2","FolderPath" and "FileName". 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.