This is a classic case of Power Query doing exactly what you told it to do, and then making a mess of your workbook because of a default setting you likely overlooked. The duplicate tables, those "Table1" and "Table2" sheets that appear after you refresh, are not a bug. They are the direct result of Power Query loading every query step into the workbook as a separate table. You created a merge query, but you also left the original two table queries set to "Load to Table" in the current workbook. Every time you refresh, Power Query dutifully creates new physical tables for those source queries, even though you only wanted the final merged result.
The practical takeaway is straightforward: you do not need those duplicate tables. They are consuming space and creating confusion. The solution is to open the Power Query Editor, locate the two queries that feed into your merge, right-click each one, and uncheck "Enable Load." This tells Power Query to keep the query in the model for reference but stop creating a physical table in your workbook. Your merged result will still update because the merge query depends on those queries as data sources. You can keep your original Excel sheets exactly as they are, the data source, and the original workbook tables can remain untouched. The merged result will reflect changes when you refresh, provided you are refreshing from the same file path.
The core confusion here is about where the "source" lives. Your original two Excel sheets remain the authoritative source of truth. The new "Table1" and "Table2" that appear automatically are just copies that Power Query generates. If you update your original sheets and then refresh the merge query, the merge will pull from the original sheets (or the file path you established), not from those duplicated tables. So yes, your merged query will automatically reflect updates from your original sheets when you refresh, as long as the query is pointing at those original sheets. The key step is to ensure that your Power Query connections reference the correct file location and sheet names, not the auto-generated table names in the workbook.
Do not let this scare you off from merging. What you are experiencing is a default behavior meant for exploration, not for production use. The moment you disable load on the intermediary queries, the duplicate tables vanish. You are left with one clean merged table that updates reliably from your original source sheets. That is the workflow you want. Now go disable those loads and reclaim your workbook.