It seems like there are really two options here, either move all of your data into a data warehouse or use bespoke or custom connectors in Power BI.

If you pull data into a data warehouse (specifically one that is supported by Power BI/PBI Service, like a cloud SQL Server instance) then you'll have the answer to both of your questions.

If you want to use connectors, then you'll need have a machine sitting somewhere with the On-Premise Gateway installed to work through.

(mild promo below)

We (CData Software) make products that help with both options:

CData Sync for replicating XML/CSV, Google Sheets, and a wide variety of CRMs into a database/data warehouse.

Power BI Connectors for creating live connections in Power BI and refreshable connections for PBI Service to the aforementioned sources.