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

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

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.

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

@@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!

@@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.

@@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?

@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

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.

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

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.

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

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

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.

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

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

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.

@@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.

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.

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.

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

@@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?

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

@@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?

@@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.