Excel and Power Automate: How to overwrite data automatically - ideas on process please

[AliPo80]

Hello all,

I have been tasked with automating a process but am stuck on how to go about achieving what is needed. Can anyone help?

• Every day, a customer sends an email containing an attachment Excel file with 2 full weeks of product forecast data e.g. from Sunday 14th April to Saturday 28th April.
• I have a Power Automate workflow set so that the attachments are saved in a folder on SharePoint every time the email is received

I have made a MASTER Excel file where I have pulled in the data from the original Excel file sent as an attachment and have used Power Query to transform the data into the required format.

Tomorrow, a new email with an Excel attachment will be received, still with the same 2 week data (from Sun 14 to Sat 28 April) containing updated figures.

I will need to automatically overwrite the data in the MASTER Excel file with the new information.

At the end of the week, the email attachment will contain data for a new set of dates e.g. Sun 21st April to Sat  4th May. This will need to overwrite the rows of data for dates 21 - 27 April but create new rows for the week 28-4 May.

Does anyone know how this can be achieved?

I hope this makes sense. Any help gratefully received as I'm stuck!

Many thanks.

[#2]

@AliPo80 

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.