This video how you can connect an Excel file to a folder with Power Query and bring the data of the most recent file in that folder into Excel. The particular situation used in this video also includes an example of how you can apply a left outer join to consolidate your data.

(Example based on a case presented on a previous video)