cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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. 

SEK_0-1599693205198.png

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. 

SEK_1-1599693916565.png

 

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

 

SEK_2-1599694057939.png

My Problem is sheduling this to keep the data current.

 

SEK_3-1599694153889.png

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

 

 

 

 

 

 

 

9 REPLIES 9
Highlighted
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Impactful Individual
Impactful Individual

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

 

 

// if this is a solution please mark as such. Kudos always appreciated.
Highlighted

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Regular Visitor

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

Highlighted
Impactful Individual
Impactful Individual

@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.

 

 

// if this is a solution please mark as such. Kudos always appreciated.
Highlighted

Interesting @samdthompson

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

edhans_0-1599703686310.png

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

edhans_1-1599703955911.png

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

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Regular Visitor

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.

 

SEK_0-1599706969129.png

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

 

Highlighted

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

Highlighted
Impactful Individual
Impactful Individual

no no, it was just ananomising the data is all

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors