Planning a Power BI Rollout - How to setup the dataset processes?

Hello!

I'm working on a solution to data analysis/reporting challenges with a Point of Sale system our company recently implemented. It's still early stages, but I'm hoping someone can simply point me in the right direction on one key issue as I get started.

There will be two key datasets - let's call it "New System Data" and "Old System Data". The goal of the project is to integrate these two datasets into one. The "Old System Data" can be cleaned/prepped one time as it's all history - no issues here.

The "New System Data" is something that will need to be updated over time with new data daily. Let's assume someone will each day export a file from the system with sales data from that day (or any arbitrary period of time). Also assume that we can export as an Excel workbook or ASCII text-delimeted file. Assume this person will do nothing more than export from one source, and place the file where they're told (dummy proof).

Once the file is placed, it will need to be transformed before finally loading into the combined dataset for use in self-service data visualizations/analysis.

With those assumptions in mind, I'm hoping someone can help me understand how I would structure the proccess of:

1. Where to place the daily data dump from "New System Data" - Sharepoint? (Keep in mind - this should be dummy proof once the data gets exported from our Point of Sale tool)
2. How to automate the Transform & Load proccess - Where does/should the combined data set get loaded to? An Access database? A massive spreadsheet? Is there something built into Power BI? 

This is a 40,000 foot question - just looking to get this critical part of the proccess outlined so I can dive deeper.

Thank you in advance!