Daily and Weekly Sales Activity Updates

Hi.

I have several questions:

I receive daily email excel attachments containing new sales report information each night via an email distribution list. About 10 sales reports each night which are all from different cost centers at different locations. To point out the obvious, each day comes with new and updated sales numbers for that day's activity. I created a PBI query set and the visuals that follow. I have been copying the M-script into each new PBI file created so that I don’t have to re-create each query step for each report. I don’t want the data to be added to the existing (previous day's) report, I want to replace it since each day's report is stand alone. Is there a better way to do this and even better automatically?

I want to update the query/visuals with the new data set each night and republish but every cost center has multiple different people that send the excel file and they all name it whatever they feel like for that day. There is no consistency on how the files are named.

If I setup a flow procedure to have the email attachment sent to a folder in onedrive, is there a way to automatically delete the previous file or do I have to manually complete this step each night? I am assuming if I do not delete the file, it will just keep adding to the existing report. That might not be a problem if I knew how to only report that day's activity without it being mixed in with the previous day's activity. How do I do that? The cumulative is fine but my owners only want to receive a notification for the day's activity. Eventually of course a weekly and monthly recap is expected as well but that would be in a separate report.

There is another broader, weekly Friday excel report also emailed via a distribution list containing inventory status of all remaining consumables, demographics data, and various other information used for the PBI visuals. In this case the same file is continuously updated by adding to the existing previous week's data. I guess it's sort of the same scenario as above where the report needs to overwrite the previous week's report so that it doesn’t duplicate.

Is there a query step that automatically replaces all of the data with the new report without adding cumulatively since each weekly report will already contain the old and new information housed in one table?

Since each transaction is dated, I can still use a timeline visual based off that date so I don’t think it matters if it is overwritten.

For the daily report referenced, is there a way to show day over day change if I am processing this new report each night through the query steps? Such as how many sales so far, and how many from yesterday? The report itself does not track anything other than the day's activity. For the daily sales report, it starts on Saturday and ends on Friday as the weekly cycle.

I just need someone to walk me through the logistics of these kind of common reporting procedures. I am more or less starting all of this from scratch because there was no one else that handled it this way other than just receiving emails daily and weekly and everyone opened up the excel sheets to consume whatever information they wanted. This is all new to me and while the logistics might be easy for some, I could be completely missing the easy solution and better way of handling the logistics.

Any help would be appreciated.

Thank you