Append daily updated data from excel sheets on network drive folders arranged by year and month to a existing workbook on one drive which has data in one list arranged in calendar dates.

[Gangsta]

Apologies if this has been posted already, a search did not find what I was looking for, mainly due to the fact, data needs to be pulled from folders based on the date the data is related to.

 What I need to do is, pull data from the correct folder on the server so it populates the sales data for each day in each column, although the setup is very similar, the columns and rows are not positioned identically.

Source date is on a network path '\\bookshop\sales\2023\January\monthlysales.xlsx'

each month a new folder is created, the sheet name stays the same, but the folder name will correspond to the year and month and will be created as new data is added by the server.

 the monthlysales.xlsx will have a sheet named sales, which will have 5 columns of data (they do not replicate the columns they sit on the source data), each row is the date, so in the example,

	Luke	Han	C3PO	Leia
01/01/2023	5	6	3	3
02/01/2023	7	2	9	5
03/01/2023	1	2	8	2
04/01/2023	2	4	1	4
05/01/2023	5	2	0	3

The new data from 3rd to 5th January 2023 in the above sheet needs to be copied to my master workbook below called 'starwars-shop.xlsx' which sits in my OneDrive folder called 'Sales' this sheet will have dates in rows from 01/01/2023 all the way to 31/12/25, the data is added on the correct row and column based on the date.

	Luke	Han	C3PO	Leia
01/01/2023	5	6	3	3
02/01/2023	7	2	9	5
03/01/2023
04/01/2023
05/01/2023
06/01/2023
07/01/2023

I am wondering if Power Automate Flows scan do this, or do I use power query in either case I am not sure how to get the variables into the workflow or script.

If it helps add the path in a helper column in my master sheet, so that the correct path can be picked up from there, so the 6th column above will have the string ‘\\bookshop\sales\2023\January\monthlysales.xlsx', I can even add the row number in the 7th column of where the data is likely to be on the source sheet as this is fixed.

Hope my request is clear and thank you for your help.