Cannot set up automatic refresh / Dynamic data source

[jct999]

Hello

I have a PowerBI report in a Workspace.

This report get data in a single Excel file that is in a sharepoint folder.
This Excel file contains some sheets where data are populated manualy, and I pull this data in the report with some PowerQuery queries.
This Excel file also contains one connection to an OLAP/MDX source that feeds a pivot table.

It is important to notice that this OLAP connection + the attached pivot table do not provide data to the PowerBI report.
They are completely independent of the rest of the file. The only data that feed the report are the manual data, in dedicated sheets of the Excel file.

Problem : It is impossible to set up an automatic refresh of the report in the workspace/PowerBI service.
The error message is : "This dataset includes a dynamic data source. Because dynamic data sources are not refreshed in the Power BI service, this dataset will not be refreshed. Learn more: https://aka.ms/dynamic-data-sources. Data source for Query1"

First tricky point : I have no query called Query1 in my PowerQuery list of queries. What is or where is this **bleep**ing "Query1" ? I don't see it anywhere !

Assuming the cause of the problem may be from the OLAP connection + PivotTable (even if they do not provide data for the report), I decided fully delete them from the Excel file.

But despite this amputation, the problem remains the same : "You cannot schedule this dataset to refresh because the following data sources do not currently support refresh: Data source for Query1 .... blablablabla... This dataset includes a dynamic data source. Because dynamic data sources ...."

Can anybody help me to understand where is the problem and how to fix it ?
What is Query1 ?

Thank you

Best

JC

[jct999]

Hi Thank you for the reply

I will no be allowed to change data sources anymore since I'm not the owner of them.

Next, the thing that annoys me the most is about the message : "This dataset includes a dynamic data source. Because dynamic data sources are not refreshed in the Power BI service, this dataset will not be refreshed. Learn more: https://aka.ms/dynamic-data-sources. Data source for Query1"

Why does the PowerBI service complain against a "Query1" that does not exist in my PowerBI dashboard !!

How can I identify the root cause of the issue with this message ?

It is a nightmare !

[v-jianboli-msft]

Hi @jct999 

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.

Please see these tutorials for more information:

Refresh and dynamic data sources

Get Excel Data from a Single File or Entire Folder on SharePoint

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

instead of removing the OLAP part, try separating the manual part out into a different Excel file and access that file via a sharepoint folder connector