PowerAutomate - Merge Excel sheets

[@MahmoudIsmailTawfik]

Thanks Markus for the great content, I created a flow to combine daily report in one monthly but for some unknown reason for me when it reach the Add a row into a table failed to get the data and I receive the below error. did you face something similar before? "The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression 'exce@{outputs('List_rows_present_in_a_table')?['body/value']}' is of type 'String'. The result must be a valid array."

[@sameersheik260]

Thanks for the video Markus, can you help me if can explain how to merge the Excel files with different columns please

[@MarkusSchiller]

Hi! I'm sorry, I don't fully understand your problem. Do you mean that your columns are named differently as in my example?

[@CarFinanceSimplified]

Very good - thanks. Do you know how I can use multiple LIST ROWS PRESENT to get fields from multiple Excel Tables and then combine the fields so that the can be added to the same row in a new table? For example, date comes from the first table, item comes from the second table and then you can add these combined to a new row on a new sheet with a DATE column and an ITEM column?

[@MarkusSchiller]

Do you need this to be a power automate flow, because it almost sounds like power pivot in excel would be better suited for this kind of action. Although it should also be possible in power automate too. It would contain multiple queries to those tables and somehow you would need some kind of common identifier so the correct values are combined from the different excel sheets

[@andythemouse]

Hi Markus, i wanted to create a similar flow based on your video but the 'when a file is created in a folder' is deprecated, do you have another solution for that? appreciate your sharing

[@MarkusSchiller]

Hi! There's another trigger called "When a file is created (properties only)" and in one of the advanced parameters you can select the folder. I guess I will need to update this video soon

[@bconnaughton2514]

Hi Markus, thanks for this video. I tried used this method to add about 2,000 rows from one table into another table. However, only about 250 rows added and the flow was marked as complete. Do you know why this might be?

[@MarkusSchiller]

Hi! I can just make some guesses, as I don't really see the flow before me. I would suggest to check in the Apply to each loop, how many iterations it did. If it did about 2000, but still only added 250, then it could be that it's a running error. In the settings for the "Apply to each" action, you can turn on concurrency control and set the degree of parallelism to just 1. As PowerAutomate makes a new request for each row entry to open and close the excel file, I've already experienced, that it can get a bit "confused" sometimes and not get the expected results. I'm still hoping that Microsoft will one day implement bulk entries of rows into excel files with PowerAutomate.

[@cs9181]

Hi Markus, this fantastic. It worked the first time perfectly. I dragged 8 Excel files into the Uploads folder and it was totally successful. One question: my Excel files do not have Tables. I had to add a Table manually prior to running the Flow. The column are always the same. IT’s just the rows that vary. Just wondering if you have created a video which creates a Table for data in multiple Excel workbooks? Thanks for your time and this video.

[@MarkusSchiller]

Hi! Thank you for your nice words. In this case I would always recommend to work with tables within Excel sheets. You can add a new table to an Excel sheet with PowerAutomate, but you can not convert an area with existing data to a table in PowerAutomate. I don't know exactly what business processes you have. But you could modify this flow in a way that it works with CSV files too and these get added to the excel file.

[@cs9181]

@@MarkusSchiller Thanks for your reply! Unfortunately, the Excel workbooks are auto-generated with data and not in tables. Each workbook is separate and the data is on the first and only sheet. Is there a way to add a table to a new Excel Workbook, then collect all of the data from the existing workbooks (perhaps upload the data to a MS List) then populate the table in the new Excel Workbook with the collected data?

[@MarkusSchiller]

@@cs9181 PowerAutomate has a "Create table" action, where you can create a table, even if there's already data in these cells. So technically you could alter the flow that whenever a new excel file comes into that folder, a table will be added. But, there's a big BUT. In order to create the table, you'll have to tell the action where the table should be. Like from A1 to D24. But if your data exceeds that, it will not be included in the table. You could also say its in all rows from A to D, but that leaves you with over 1 million rows and a lot of "empty" data. So, you have to make a guess. If your data never exceeds 1000 rows, you could fill in the action the range A1 to X1000. It will still give you sometimes a lot of empty rows, but that's easier to process, than 1 Million rows. Unfortunately there's no perfect solution for this in PowerAutomate and you'll have to work around it's limitations.

[@cs9181]

@@MarkusSchiller Markus, thanks for your time and knowledge. I think your answer will be a perfect solution for me, as the data in each workbook will never exceed 100 rows.

[@christopherskyap8985]

Hi Markus, tot, the flow suppose to move to done folder ? I encounter "'Shared Documents/Upload/MonthlyUpdates - Copy.xlsx' cannot be moved because it is in locked mode.

[@MarkusSchiller]

Yes, that's what I mentioned in my video. You'll probably have to insert a delay in the flow, but I didn't have time to put it in the video

[@MarkusSchiller]

I had time to look a bit deeper into it and it would work with a simple delay, but of course you'll never really know how long to wait for it. So you can make a "Do until" loop with 3 actions inside. The "Move file" action after that a "Delay" action (let's say one minute) that only triggers, when "Move file" failed and a Set variable action that only activates when "Delay" was skipped. The set variable simply sets a variable that you have initialized before to true or false and the "Do until" loops breaks if that specified value is set.

[@christopherskyap8985]

@@MarkusSchiller Hi Sir, so just put 3 in the count and save it ?

[@jou352]

Hi Markus, Thank you very much, for your Help! Now I can solve my problem with this steps. If I try to use the manually flow instead the automated flow, and the second step, I use get files (proprieties files), Will it work? Thanks once again

[@MarkusSchiller]

Hi! Yes, it would also work in the way you've described it :-)

[@andrewcadavid429]

@@MarkusSchiller Hi Markus! Thanks for the helpful vid. Could you expand upon this step a bit. Power automate automatically puts your output within a "Apply to Each" step and it is a little more involved than your original explanation in the video. Not sure if I need to make the output look like "x-ms-file-id" @jou352, did you get this to work how you wanted? If so can you explain please. Thank you!

[@MarkusSchiller]

@@andrewcadavid429 Hi Andrew, thank you if you found the video helpful, but could you explain your problem with a bit more details, because I don't fully understand it.

[@andrewcadavid429]

@@MarkusSchiller When I use the action "When File is created in the folder", the next step after allows you to pull a "x-ms-file-id" dynamic content value which can be used in the next action of "list rows present in table" for the "file" of that action. This does not work the same with using "get files (Properties only)" action. I was wondering how to get the same result using a manually executed flow. For some reason I cant get the same results when using the manual flow instead of the automatic " When files are created in folder". Where would you put the "get files" action and how would you use that in the subsequent steps?

[@andrewcadavid429]

@MarkusSchiller I suppose the main question is what goes into the file textbox for the list rows present in table if it does not automatically come up as "x-ms-file-id". Referring to your video, what you say at 4:40 - 4:45 does not happen when you use the "get files" action in the flow. Trying to manually enter: "triggerOutputs()?['headers/x-ms-file-id'] does not work

[@fvcerullo]

The "When a file is created" step for SharePoint folders is now listed as [deprecated] by Microsoft. "This action is deprecated and may not work as expected."

[@MarkusSchiller]

Thank you for the notice, I think i might update this in the future. I've checked it and it has been replaced by another trigger that's quite similar.

[@saragallimore7568]

@@MarkusSchiller can you tell me what is the name of the new trigger?

[@MarkusSchiller]

@@saragallimore7568 Hi Sara, it's almost the same: "When a file is created (properties only)"

[@MatheusFerreira-jp5ex]

Hi Markus, awesome video! I tested here, and after a first run, the expression "item()/['example1']", turns automatically to an excel "item('apply_to_each)['example1']" and after that, the flow doesn't add the content to the table. do you have some idea how to work on this?? thanks again Markus!!

[@MarkusSchiller]

Hi, thank you for the nice comment. It's a bit hard to determine where that problem could be. First of all, it's perfectly normal that PowerAutomate turns item()?['something'] into items('Apply_to_each')?['something']. Both expressions are doing the same thing in this case. 1. Do you get any error rom the action "Add row into a table"? 2. Is there any output in your previous action "List rows present in a table"? 3. Have you opened the excel file? Sometimes it needs about 10 minutes for excel online to set a file free for other editing actions.

[@General_halap]

Awesome, seems you are about to save me😅

[@bolor-oyu1809]

Hi Markus, i want to hugely thank you for you for creating this video. I rarely put a comment on videos. I was desperate to figure out the task I was trying to do for past few days and could not find any relative video/tutorial. Finally found your video and is quite relevant to what I am trying to achieve. I really want to say keep creating the video. You might get bit disappointed on the number of subscribers or number of video views, but you never know how much you have helped someone without knowing :) I have a question, if you don't mind. What I am trying to do is very simple but don't understand why mine is not working. I have 10 projects ongoing. Each project has a separate folder in sharepoint. I have created a standard Project status excel in each project folder. And in a separate folder, I have Overall project status to cover key infos about the 10 ongoing projects. Since Project status requires the data to be in table format, it is in the table format, however there are 5 different tables in each file (table 1: project intro details, table 2 overall project health, table 3 tasks accomplished this week, table 4 risks, table 5 next week tasks). If possible I want to get only certain columns of these 5 tables into my Overall Project status excel file. However, if not, I can have combined table of all 5 tables data of all 10 projects as one. from there, I can create a dashboard to track the status. Hope you understood my case. is there any simple way to get this done? I need this template ready on 8 May, so just in case if you are available, your help sooooooooooooooo much appreciated.

[@MarkusSchiller]

First of all, thank you for the kind words. I have some jobs that I have to do, so unfortunately, I don't always find so much time to make the videos, otherwise there would be already a bit more content. There are a few ways to work on this problem. As I understand correcty, your overall status excel file and the individual excel files within the project folders have the same table structure. Five individual tables within each file and the names are always the same? The easiest way would be to use the "List rows present in a table" action. Then select the excel file within the "Project 1" folder and select the table. Caution! If you have more than 256 rows in that table, you will need to click on the three dots, go to "Settings", Turn On "Pagination" and set the maximum Threshold (like 2000 rows). The next step would be to use "Add a row into a table" and select your overall status file and the correct table where the data should be put in. PowerAutomate will automatically generate an "Apply to each" loop, because it's an array. From there on you could copy and paste these two actions and use it for each table within the project. If you have 10 Projects with each 5 project tables, then you'll have 50 times these steps. It would be a bit more elegant to use custom arrays. One array for your Projects 1 - 10 folder structure. And one array that would combine the data from, let's say "tasks" from all ten project files and then use this array as the source for "Add a row into a table". The problem is that this flow would always add all the rows over and over again, each time you run it. You would need to implement some kind of status of each row, that it has been already importet and then use a Filter Query in the action "List rows present in a table", so it only collects the rows that were not imported yet. Another solution would be to use Excel scripts to delete all the data in your Overall Status file and then it doesn't matter if the flow always imports everything. There is a hitch to this, as Excel scripts can only be triggered if the Excel sheet is stored in OneDrive and not on SharePoint. At least it was like this a few months ago. Sorry, I won't have enough time to make a video about this until the 8th of May.

[@bolor-oyu1809]

@@MarkusSchiller Thank you soooo much! I did not expect that prompt & detailed answer. There is one thing that I have forgotten to mention, and not sure if it make any difference. The Status reports have 5 tables for each project. But in overall project dashboard, I have only one table that has all the columns in 5 tables. (say I have 4 columns for each table and in overall one I have 20 columns as one table.) (each table has max 5 rows only) I followed your instruction (which I have done previously as well). Currently, it says the flow ran successfully, but it is not getting values from each table cell. The overall project dashboard table active rows are increased based on the number of times I tested it, but all cells are blank. Steps I took: Manually trigger a flow -> List rows present in a table (for each table) -> Add a row into a table (expression: item()?['PROJECT NAME'] etc for each column) Questions: 1. I am a totally beginner and don't have much knowledge. What exactly I should write in Filter query: " to use a Filter Query in the action "List rows present in a table", so it only collects the rows that were not imported yet. " From my previous tests I noticed exactly what you said. The updated fields created new rows instead of updating existing one in overall project dashboard. Is there any other ways to avoid this? I want the relevant columns get replaced instead of creating additional rows. 2. In order to get the latest updates in my overall project dashboard, I guess I need a separate power automate for "once Project status get updated every week, the copy is saved in the different folder as a separated file and saved as Project name_Weekly Status Report_Date" So that we can keep the record of previous weeks status reports + Power automate will not look for any other files in that folder. 3. Is there any chance to get several tables from one file instead of creating flows of 5 times for each project status file? Sorry for bugging you. Have a wonderful weekend.

[@bolor-oyu1809]

Just one more thing that i noticed now. In the status report of 5 tables, table 1 has project intro details such as name of projects, date of project status entry etc (only 1 row), and table 3 has list of tasks completed this week (5-10 rows); table 4 potential risks (3-5 rows). The main reason I am creating Master table in Overall project status is I want to create a dashboard - and will made using pivots. So how can I say to excel that all the table 3 rows are relevant to project name that is on row no.2? The same logic applies to the rest of the other project statuses? Or all the efforts that i am trying to do to create Master table can be done Power query instead of Power Automate? If power quiry can do it, how can it can get all the data from different folders in a sharepoint? Thank you very much.

[@MarkusSchiller]

I also thought that maybe power query is a better solution for you, but of course you'll need some kind of identifier which row belongs to which project.

[@quorthunsargeras7054]

This only adds 1 row for me.

[@MarkusSchiller]

Well, that can have multiple reasons. Either your query only outputs one item, or sometimes it takes excel online a few minutes to add other rows

[@quorthunsargeras7054]

@@MarkusSchiller Thanks for responding so fast wow! I have mine literally setup exactly the same way you do. How do I check if my query only outputs one item?

[@quorthunsargeras7054]

@@MarkusSchiller Additionally, the flow is completing successfully. So I know it's running all the way.

[@MarkusSchiller]

@@quorthunsargeras7054 if you open the successful flow run and go to the apply to each action you should see some text indicating like 1 of X run. It's hard to say as I don't have access to your table. But is the data really within the table in the excel sheet?

[@quorthunsargeras7054]

@@MarkusSchiller Thanks, I figured it out. For some reason I'm an idiot and didn't see the arrow button there that lets you click on each record. It added 315 rows... The rows are showing up with the values being the NAMES of the columns which is very weird. That master spreadsheet I use as my primary Power BI Source. For some reason, the rows were added perfectly into my Power BI Source. Why? I have no idea... But it's all I needed for now. That "Master" spreadsheet and lay there and rot as a backend Power BI source for all I care.