Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
"
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.
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.
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.
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.
Welcome to the joys of the Formula Firewall !
Behind the scenes of the Data Privacy Firewall | Microsoft Docs
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.