Dataset will not refresh

[Anonymous]

I have a simple Dataset that contains 2 tables. one table has a list of all my databases and the other is a dynamic function (see code below). I Invoke a custom function on database single column table. I assign the single column to my "DatabaseText" variable and everything works great. BUT

When I publish this to the service and try to schedule a refresh I get this error "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. " I believe you can use "[Relative path" to get around this but I don't know how. Can someone help me? If you can't use "Relative Path", then is there another solution. The power query is looping through every database and providing me all the dependencies. See my power query code below:

= (DatabaseText as text) =>

let
Source = Sql.Database("az-pdwsqls01", DatabaseText, [Query="SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, #(lf) o.type_desc AS referencing_desciption, #(lf) COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,#(lf)#(tab)s.name COLLATE DATABASE_DEFAULT aS schema_name,#(lf)#(tab)t.name COLLATE DATABASE_DEFAULT as view_name,#(lf) referencing_class_desc, referenced_class_desc, #(lf) referenced_server_name, referenced_database_name, referenced_schema_name, #(lf) referenced_entity_name, #(lf) COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name, #(lf) is_caller_dependent, is_ambiguous #(lf)FROM sys.sql_expression_dependencies AS sed #(lf)LEFT JOIN sys.objects AS o ON sed.referencing_id = o.object_id#(lf)Left JOIN sys.views t#(lf)ON t.[object_id] = sed.referencing_id#(lf)LEFT JOIN sys.schemas s#(lf)on s.schema_id = t.schema_id"]),
#"Added Custom" = Table.AddColumn(Source, "Reference Database and Table Name", each [referenced_database_name] & "." & [referenced_entity_name]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Reference Database and Table Name", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([referenced_entity_name] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"referenced_database_name", "REF Database Name"}, {"referenced_schema_name", "REF Schema Name"}, {"referenced_entity_name", "REF Object"}})
in
#"Renamed Columns"

[v-rongtiep-msft]

Hi @Anonymous ,

• It looks like you will have to change your datset a bit so you can access the network path using on-premises gateway.Please go through below link which will give you an idea:Chris Webb's BI Blog: Configuring Power BI Gateway Data Sources For Files And Folders Chris Webb's BI Blog (crossjoin.co.uk)
•  Power BI cannot validate the URL of the query. You have to make the URL static and then use the Relative Path Dynamic.
  
  You will have to look at the link and have to change the underlying query.

I have also found a similar post, please refer to it to see if it helps you.

Solved: Help: Dataset includes a dynamic data source error - Microsoft Power BI Community

If it still does not help, please provide a screenshot with the error.

Best Regards
Community Support Team _ Polly

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

[Anonymous]

@v-pn  Thank you for your quick response. The first article you posted does not help because I am not dealing with a folder. The second resource you suggest is dealing with "Web.Contents" so it is not helpful. I know about "RelativePath" and I know how to use it when using "Web.Contents". The problem is now I need to figure out how to use it with "sql.database" as you see in the "M Code" in my first post. Can you help me know how to update my M so that it will refresh?

[v-rongtiep-msft]

Hi @Anonymous ,

Do you seem to use import to connect to sql.database?

Please have a check. To determine whether your dynamic data source can be refreshed, open the Data Source Settings dialog in Power Query Editor, and then select Data Sources In Current File. In the window that appears, look for the following warning message, as shown in the following image:

Data refresh in Power BI - Power BI | Microsoft Learn

If the warning does not exist, please provide the screenshot.

Could you please provide you pbix file without privacy inforamtion for me to test?

Best Regards
Community Support Team _ Polly

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

[Anonymous]

@v-rongtiep-msft yes as you suggest I get that error. I am hoping there is a work around just like there is a work around for web.contents by using relative path? I will work to post the file. Thanks,

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

The documentation describes:

If that warning is present in the Data Source Settings dialog that appears, then a dynamic data source that can't be refreshed in the Power BI service is present.

Best Regards
Community Support Team _ Polly

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