Unable to refresh data in power Bi service while connecting to sharepoint list and document folder of all listed sites

user_3148367 0
Viewed 1298

Hi I am currently trying to develop a reporting tool. There is a SharePoint online list which has various SharePoint sites in that list. My objective is to retrieve all SharePoint sites from that list and to connect to the documents folder of all retrieved SharePoint sites. I am able to connect to all documents in Power Bi desktop but the refresh fails on the Power Bi service saying - Unable to refresh the model because it references an unsupported data source.

Here is the logic that i am using to connect to the document folder of all sites.

Main Query -

let
       Source = SharePoint.Tables("https://xxxxx.sharepoint.com/sites/Projects/", [ApiVersion = 15]),
       #"xxxxxxxxxxxxxx" = Source{[Id="xxxxxxxxxxxxxx"]}[Items],
       #"Renamed Columns" = Table.RenameColumns(#"xxxxxxxxxxxxxx",{{"ID", "ID.1"}}),
       #"Expanded SiteUrl" = Table.ExpandRecordColumn(#"Renamed Columns", "SiteUrl", {"Description", "Url"}, {"SiteUrl.Description", "SiteUrl.Url"}),
       #"Removed Other Columns" = Table.SelectColumns(#"Expanded SiteUrl",{"Title", "Id","SiteStatus","ProjectCode", "SiteUrl.Url"}),
       #"Documents" = Table.AddColumn(#"Filtered Rows2", "Documents", each GetList([SiteUrl.Url], "Documents"))
in
         #"Documents"

Below is the code for GetList function -

= (siteURL,listname) =>

    let

        Source = SharePoint.Tables(siteURL,[ApiVersion = 15]),

        #"MyListData" = Source{[Title=listname]}[Items]

    in

        #"MyListData"

I have taken help from this article which is very well written. https://marque360.com/aggregating-sharepoint-list-data-in-power-bi/ I am not sure why this works on Power Bi desktop but says unsupported data source on Power BI service. Could anyone please guide me on how to get this refresh working on Power BI service.

powerbi-datasource sharepoint-list powerbi-desktop powerbi
1 Answers

Try using "Auto" value for ApiVersion in query. Connection support for Sharepoint

When entering the URL for the SharePoint Lists, enter the root Site Collection URL and then provide the correct credentials, say the LDAP login credentials.

Enter the URL with full path (http:///app/_api/web/Lists/GetByTitle('')/Items?$select=)

Ref: Syntax

SharePoint.Tables(url as text, optional options as nullable record) as table About Returns a table containing a row for each List item found at the specified SharePoint list, url. Each row contains properties of the List. options may be specified to control the following options:

ApiVersion : A number (14 or 15) or the text "Auto" that specifies the SharePoint API version to use for this site. When not specified, API version 14 is used. When Auto is specified, the server version will be automatically discovered if possible, otherwise version defaults to 14. Non-English SharePoint sites require at least version 15.

If you created your datasets and reports based on a Power BI Desktop file on SharePoint Online, Power BI performs another type of refresh, known as OneDrive refresh. For more information, see Get data from files for Power BI

Unlike a dataset refresh during which Power BI imports data from a data source into a dataset, OneDrive refresh synchronizes datasets and reports with their source files. By default, Power BI checks about every hour if a dataset connected to a file on OneDrive or SharePoint Online requires synchronization.

Note: It can take Power BI up to 60 minutes to refresh a dataset, even once the sync has completed on your local machine and after you've used Refresh now in the Power BI service.

The dataset settings page only shows the OneDrive Credentials and OneDrive refresh sections if the dataset is connected to a file in SharePoint Online, as in the following screenshot enter image description here. Datasets that are not connected to sources file in SharePoint Online don't show these sections.

In most cases, Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

For refresh issues related to dynamic data sources, including data sources that include hand-authored queries, see refresh and dynamic data sources

user_159694170
Related