Problem with scheduled refresh when using dynamic sql query

[goalie_]

Hi all,

For a dataset, I am using a dynamic sql query for a table. I have a separate list of items and for the query, I am using a text.combine(list, "','"). Similarly like in the link: https://radacad.com/dynamic-sql-using-power-query The refresh does not fail when I have a static list in place. 

However when I schedule a refresh, it fails. I get the message below. Any ideas how to proceed?

"

Something went Wrong

Unable to connect to the data source undefined.

Please try again later or contact support. If you contact support, please provide these details.

• Underlying error code-2147467259
• Underlying error message[Unable to combine data] Section1/Table/Added Custom references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
• DM_ErrorDetailNameCode_UnderlyingHResult-2147467259
• Microsoft.Data.Mashup.MashupSecurityException.DataSources[{"kind":"SQL","path":"server;database"}]
• Microsoft.Data.Mashup.MashupSecurityException.ReasonPrivacyError
• Cluster URIWABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
• Activity IDaa154c2d-410c-46d9-b038-750d8377a032
• Request ID46d8d9b4-a457-454a-a265-695b9c0c83c4
• Time2020-11-04 15:43:01Z

"

[v-janeyg-msft]

Hi, @goalie_ 

You can try to check and change it:Desktop——Files——Options and Settings 

If the formula is correct,and it can be refresh when using static list.It may be privacy security issues.

When you connect multiple dynamic data sources, then refreshing may cause data leakage, so refresh failed. You can try to set the privacy level(service——manage gateways——datasources settings——advanced——public) of all related data sources to public if you allow.

If it still doesn't work, it may not be supported, and the data must be modified to meet the standards.

Best Regards

Janey Guo

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

[lbendlin]

Please read the article that I referenced. There is no simple fix, you need to thoroughly understand the subject to make your decision on how to implement your solution.

[v-janeyg-msft]

Hi, @goalie_ 

It’s my pleasure to answer for you.

According to your description,There may be something wrong with 'Added Custom references other queries or steps' in PQ.

Please check your formula.Could you mind providing more information about it?

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[goalie_]

Hi @v-janeyg-msft , I don't see anything wrong with the formula in power query. There is absolutely no issue in desktop. Only when I refresh it in service. I don't really understand @lbendlin 's answer. An action solution would be appreciated rather than a redirect to some documentation.

Here is a sample of what I'm using. There is only one step in the table, which is "Source":

= Sql.Database("Server", "database", [Query="Select * from Table where column in ('" &Text.Combine(ListOfItems, "','") & "')"])

The ListOfItems is a one column table(manually entered) which I converted into a list. 

I'm not sure if it's some sort of privacy setting issue that I can hopefully just change. 

[lbendlin]

Welcome to the joys of the Formula Firewall !

Behind the scenes of the Data Privacy Firewall | Microsoft Docs