Forum Discussion
Excel and Power Automate: How to overwrite data automatically - ideas on process please
To be sure
- you have work file in SharePoint folder which is updated from time to time by Power Automate
- you have master file somewhere outside
- Power Query within master file gets information from work file and loads transformed data into the sheet within master file
- next work file could repeat or update information which is already exists in master file and/or add new information.
If so, transformation could be done using self-referencing table technique, the basis is explained here Self Referencing Tables in Power Query :link:- Excelerator BI
The only you need to have the column with unique ID or ability to create such kind of column. That could be column merged from columns with dates, texts, whatever. Important it shall be unique for all final records.
Assume you already have table returned by query (master query) in master sheet. Query again that resulting table, let say it'll be masterOld query. Duplicate master query to to new one (masterNew) and make sure both are loaded as connection only.
Merge masterNew with masterOld in new query (connection only) on key column with left outer, don't expand resulting column, just remove it.
Add index column starting from 0 to masterNew and starting from 10000 (any number greater than max possible number of records) for masterOld.
Append both above as another new query, sort on index, remove duplicates for key column, do cosmetic (remove Index, sort, etc).
Copy the code of the latest query to master query which loads result to the grid, may delete "another new" query.
Above is just idea, details depend on concrete data structure.