Forum Discussion

Toll's avatar
Toll
Helper I
3 years ago

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

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?

Need Help

5 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    3 years ago

    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's avatar
      Toll
      Helper I
      3 years ago

      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's avatar
        ibarrau
        Super User
        3 years ago

        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