Forum Discussion
Iterate SQL query based on list of sources in another table
I did a little more digging (and testing). Looks like you might be running into this issue.
https://aka.ms/dynamic-data-sources
The only way to get around this is to use parameters and as far as I know, you can't use dynamic parameters with a SQL data source. So you'd be back to square 1 with a solution that isn't dynamic having to define each source as a parameter.
You may be able to do something with dynamic SQL, unfortunately I don't have the time to try. To be honest, even if that worked you'd probably want to ask the question "should I?" for obvious reasons.
It looks like the 'E' in your ETL may need to be external to Power BI.
Yes, they are consistent and I easily adapted your solution changing the server names.
The query works within PBI Desktop but when it's deployed on report server, data sources are not recognized. Because within PQ script, data sources are not explicitly defined, it's parameterized and source is not known until after it's executed and the string from servers table are read.
I did a little more digging (and testing). Looks like you might be running into this issue.
https://aka.ms/dynamic-data-sources
The only way to get around this is to use parameters and as far as I know, you can't use dynamic parameters with a SQL data source. So you'd be back to square 1 with a solution that isn't dynamic having to define each source as a parameter.
You may be able to do something with dynamic SQL, unfortunately I don't have the time to try. To be honest, even if that worked you'd probably want to ask the question "should I?" for obvious reasons.
It looks like the 'E' in your ETL may need to be external to Power BI.
Yes, that's exactly what I'm experiencing. Thanks for your time. Maybe first step should be done outside PQ as you say.