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

[Anonymous]

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

[samdthompson]

Hmmmm, try editing the url part to be the actual url you are wanting to query:

let
Source = SharePoint.Tables("http://xxx.co.nz/sites/projects", [yyy = 999]),

in

Source

[Anonymous]

Hi samdthompson,

 what is the [YYY=999] represent in your solution ? I assume that YYY is a variable and that 999 represents the completed URL? 

Does this work for you?

Thanks

SEK

[samdthompson]

no no, it was just ananomising the data is all

[edhans]

It has to be the site. The Navigation step actually loads the table/list @samdthompson 
I've not tested specifically with SharePoint.Tables, but SharePoint.Contents and SharePoint.Files both error out unless the URL is strictly the top level URL for the site.

[samdthompson]

@edhans correct, you can however just leave out the first line of code. My solution is one that I am currently using and I know it works. I have anonymised the URL etc.

[edhans]

Interesting @samdthompson

If I connect with anything but the standard URL that has the tenant and site name, I either get

or the OK button is disabled telling me I need to shorten the URL to just the site.

This is SharePoint online from Office 365. I am not sure how it works in the case of SharePoint on prem.

[edhans]

SharePoint.Tables is supported, that is a SharePoint List. 
But there are issues with the service and dynamic sources. You may want to read this article, and all related articles it links to to see if you can work through it.

If your Lists were consistent, you could manually connect to each and Append - Table.Combine(). But if new lists are constantly getting added with each project, then that wouldn't work, and probably why you went the dynamic route as you did.

[Anonymous]

Hi Edhans,

 Thankyou for your feedback, I will work though the links you have provided however given the simplicity of the objective there may be a different solution to this problem. With answer to your assumptions you are correct that the subsites are created as required and therefore there is no consistent site. 

Thanks
SEK

[Anonymous]

Thankyou to all who have contributed thus far with this solution;

Part A.

We have achieved mild success, the BI can be scheduled when using the full path thus confirming your advise that the URL needs to be complete for the scheduler to analyse the existance of the site before being scheduled.

Part B. 

 Now I'm stuck trying to "Fake it out" as is the solution recommended.

Above commented is the example from the linked sites above where in use of web.contents the method has been used to substitute a portion of the URL; This should also be possible where the base of my sharepoint site is the PWA and subsite then would need to be applied as the peramiter. 

I have changed the value of "siteUrl" above to be the name of the subsite only and used [relativepath="/" & siteURL] as also commented above. however at this stage the substitution is not working and therefore returning a blank set of data.

Can anyone suggest how this substitution should look ?

Thanks

SEK