Scheduled Refresh with Web/SharePoint Folder connector pointing to Excel in SharePoint

[Toll]

Hello,

I am trying to publish a report and configure scheduled refresh.

The dataset is comprised of some SharePoint Online List connectors and some OData Feed connectors as well as a connection to an Excel (.xlsx) file hosted within a SharePoint folder.

When working within Power BI Desktop, the report is fine and all data refreshes okay; but when I publish to the service I am met with an error that I cannot configure scheduled refresh:

" This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources. "

I understand the 'dynamic data source' to be the Web/SharePoint Folder connection.

I also get this error within the 'Settings' of the dataset:

" You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

• Data source for Query1"

None of my queries are called 'Query1'...

I have tried clearing the credentials and tried using multiple Windows accounts for authentication (using Organisation login doesn't work) but still no joy.

I am pretty certain I have seen instances where people scheduled refreshes for Excel files hosted in SharePoint.

If I go to 'Data Source Settings' it says at the bottom "Some data sources may not be listed because of hand-authored queries".

The query in question is likely the Web/SharePoint Folder connection that reads the Excel file then merges with another table.

Is there are workaround or known solution for this problem?

[ibarrau]

Hi. A dynamic data source usually means that your source doesn't have a static definition. It means you might be changing the url for the source. For example : one of the most frequent dynamics sources is when using Web.Contests for getting data from API or URLs we tend to use parameters like 

Web.Contents("https://web.com/whatever?year=" & param)

Desktop will just run the query, but Service want to identify the unique source and can't, because it doesn't know if it's "https://web.com/whatever?year=2022" or "https://web.com/whatever?year=2023" when running the query.

We might be able to help more if you share the source power query code for that.

In order to fix the previous message I have send it is recommended to use RelativePath. You can check the practice in the following post: https://blog.ladataweb.com.ar/post/630597294839955456/powerquery-buena-práctica-para-un-web-request

I hope that helps,

[Toll]

Hello,

Thank you for your reply. Sorry if I have misunderstood, but the only query using Web.Contents() is below:

let    
    source = Excel.Workbook(Web.Contents("https://[site].com/.../Deliverables/Power%20BI%20Reporting/File.xlsx"), null, true),
    Table1_Table = source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Team", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, dimUsers, {"Name"}, "dimUsers", JoinKind.LeftOuter),
    #"Expanded dimUsers" = Table.ExpandTableColumn(#"Merged Queries", "dimUsers", {"Users.ID"}, {"dimUsers.Users.ID"})
in
    #"Expanded dimUsers"

But that was also because using:

 Source = SharePoint.Files

Didn't work either..

[ibarrau]

I don't think that's the one with a mistake because it looks like it points to one url. The way of connecting it's perfect, you don't worry. What other table sources do you have in there? there is a dim users, what data sources are you using?

Regards

[Toll]

Thank you for explaining.

dimUsers points to a parameter then basically does a "for each" loop on multiple SharePoint URLs, separated by commas. I assume this is the problem now.

Is the only way to fix this by hardcoding the URL and creating multiple queries for each SharePoint URL? Or is there a better way?

let
    SharePointURLParameter = SharePointList,
    SharePointURLs = Text.Split(SharePointURLParameter, ","),
    appendedURLs = List.Transform(SharePointURLs, each _ & "/_vti_bin/listdata.svc"),

    createQuery = (url) =>
        let
            ODataSource = OData.Feed(url, null, [Implementation="2.0"]),
            userinfoTable = ODataSource{[Name="UserInformationList", Signature="table"]}[Data],
            appendedTable = Table.AddColumn(userinfoTable, "CustomerSharePointCode", each Text.BetweenDelimiters(Text.AfterDelimiter(url, "/cases/"), "/", "/_vti_bin/listdata.svc")),
            combinedColumn_TeamID = Table.AddColumn(appendedTable, "CombinedID", each Text.Combine({Text.From([Id]), [CustomerSharePointCode]}, "-"))
        in
            combinedColumn_TeamID,

    createQueries = List.Transform(appendedURLs, each createQuery(_)),
    combinedQuery = if List.Count(createQueries) > 0 then Table.Combine(createQueries) else #table({"DummyColumn"},{}),
    #"Removed Other Columns" = Table.SelectColumns(combinedQuery,{"CombinedID", "Name","WorkEmail","ContentType"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"CombinedID", "Users.ID"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"ContentType"})
in
    #"Removed Columns"

the refresh works perfectly in Power BI desktop ☹️ but I suppose the service can't determine the URLs via parameter

[ibarrau]

That's definitely the source with the issue. Power Bi Service can't specify the source for that OData. This is a tricky case because I'm not sure you can split url with good practices for ODataFeed... If you can get your data with Web.Contents instead of OData Feed, we might be able to build something like

Web.Contents("https://web.com/", [RelativePath="whatever?year=" & param])

The example will help power bi to think the source is "web.com" even though you are changing the whole picture with the path. I don't think odata has that. If you can get your data with this other way, you might be able to avoid "Dynamic source" error.

I hope that helps,