problems using SharePoint.Tables(siteURL) to schedule a data refresh

Hello Everyone,

 This being my first post ever,  please be kind...

I have recently started using Powerbi to report on data collected using project 365 online. 

I have been able to successfully create a template site for the data needed for our projects which is stored in a project online subsite in the form of a sharepoint list. My task now is to report on the content of data for our executive in order that they can review the status comments, Accomplishements, Activities Planned etc held in sharepoint field on each subsite. 

These fields do not exist in the out of the box sharepoint project site and are part of our KPI reporting requirements.

Having now created a list in the template site, each new project now has a location to keep a running update of the required project KPI requirements.  Thusfar all good.

My next challenge is the obvious reporting this data to the executive. My Exec will not be wanting to open each project subsite to drill into the required week within the sharepoint list to review the data - therefore PowerBI analysis.

After searching the web for a way to connect to the subsite lists I came accross a solution which works from my powerBI desktop. This solution iterates through each subsite from the project online lists available and returns the data from each subsite and works successfully. 

The code that has been called to loop each subsite is as follows;

My Problem is sheduling this to keep the data current.

Is there another way to achieve my objective without needing to refresh the data locally and publish it each morning.

What alteration is needed to enable SharePoint.Tables to be a supported data source?

Thankyou to all and any who can assist.

Kind Regards

SEK