There's a better way to do this, and it doesn't require learning a new tool from scratch. The weekly routine described here, downloading two files, moving them to a reporting folder, then manually updating Power Query sources, is common, but it's also entirely avoidable. The frustration isn't with Power Query itself; it's with the manual steps that have become invisible habits.
The fix for the first question is straightforward: point Power Query at the folder, not the file. Instead of connecting to a specific Excel workbook, create a query that reads all files from your reporting folder. Power Query can combine all tables or sheets that share the same structure, automatically picking up whatever is in that folder when you refresh. This eliminates the need to open the editor, find the source step, and type a new file path. You just drop the new downloads into the folder and refresh the final report. The query logic stays the same; only the data changes.
The second question, retaining old data for week-over-week comparisons, requires a small structural change, but it's one that pays off immediately. Power Query is designed to load the latest snapshot, not a history. To keep previous weeks, you need to append rather than replace. One approach: load each week's raw data into a separate table in your final report, then use a date column to filter or compare. Another option: store a cumulative table in a separate workbook or database table that appends new rows on each refresh. Neither is complex, but both require deciding upfront how you want to track change. Without that decision, Power Query will happily overwrite last week's numbers every time you refresh.
This user is already doing the hard part, they understand the merge logic. The remaining friction is purely procedural. By shifting from "update the source" to "refresh from the folder," and by adding a simple append step for history, the weekly report becomes a single click. The time saved isn't just a few minutes; it's the mental overhead of remembering the steps, checking the file names, and avoiding the error where you point to the wrong workbook. That cognitive load is the real cost, and it's one that a folder-based query and a history table can eliminate entirely.