Urgent - how do I build a master tracker?

[Jex204]

I have several workbooks that are automatically updated with new rows throughout the day, but for administrative purposes I need a master tracker to collate a few specific columns in multiple tables on a single workbook. I need it to automatically add a row every time a row is added from the source workbooks.

I've been trying to use Get Table and Get Rows guides but I've hit a wall. Does anyone know how I could go about this?

Edit: sidenote I put together a workbook using power query to collate the source data & columns I need, which has worked in a pinch but having to manually refresh the query connections has posed challenges with the team 

[grantjenkins]

I was actually thinking Power Query before I got to your comment about already setting that up.

As far as I know there isn't an Excel Online trigger in Power Automate for when a row is added/created, so not sure how you would go about this if starting from Excel. Not sure it's possible with Power Automate Desktop either.

Question:

• How many rows are there in each Excel file, and how many Excel files?
• What do you mean by "challenges with the team" with regards to Power Query option?

Alternatives:

• Is there a possibility to store the data in SharePoint Lists or Dataverse Tables instead of Excel?
• Have a scheduled/manual flow that you can run to loop across all Excel files/rows and build up a new Excel file, or CSV. Similar to the Power Query issue where you would need to manually refresh when needed.

[Jex204]

In my test flow I've been trying to use "When a file is created or modified in SharePoint" as the trigger, with some success but I'm having trouble trying to collate the few specific columns I need for this project.

A former coworker built a flow like this a few months back, and with the same source spreadsheets no less, but the columns he selected were for analytics not administration - I know there must be a way to do it I just don't have access to that flow anymore so I can't reverse engineer his work.

Power query has worked in a pinch but a member of the team struggles with excel, having to manually refresh the query connections has caused some challenges - having an automatic flow would save me from having to jump in and give updates on numbers throughout the day.

[grantjenkins]

That would be an interesting approach using When a file is created or modified on the Excel file. What happens if someone updates an existing record, or deletes a record, and doesn't actually create a new one?

If you knew that it was only being added to, then it might work, but still need to cater for multiple rows added before the flow(s) kick off (always a slight delay before they actually run) as you won't know which row corresponded to each flow run. I guess you could work backwards from the last row to see if it was already added to your master list and if not then add it - then move up to the second to last record and same check, etc. but seems a bit prone to errors.

[grantjenkins]

If they're after numbers (stats) could you have a Power BI dashboard that uses the collated data and embed the dashboard into a SharePoint Page with a button they can click on (inside the dashboard) to refresh the data? Would be as easy as click and wait.