sql.database , Can't save dataflow One or more tables references a dynamic data source.

[HoangV]

I cannot find a way around this and need some guidance. Below is the function I am calling from my queries.
The problem I am having is that i need someway to grab the host_id_address in the srcDealers table. the IDX is pass through the function . The function works fine if I invoke it, and it works fine when handling in power bi desktop as well as during creation of the data flow. BUT i cannot save it because the 'Connection' is a dynamic value coming from another table.
Unless I am passing the statis text it will not succeed.

So i've tried passign @parameter , where the parameter value is a single entry - works fine as well even when saving the data flow.
My question: Can this be improve in someway that i can pass the ip/hostname to the connnection variable without data flow compaining when i am saving the flow? 
OR is there someway i can create some parameter (Conn1, Conn2, Conn3..etc)
and a function to combine text of "Conn" and idx, and then call that parameter's value:
ie : let connection = (idx as number) let v="Conn" & Text.From(idx) in v,
then somehow use "connection" but get the value of parameter Conn1 instead of the text "Conn1"  (basically reasignment of the parameter connection = conn1? 

Help?

(idx as number,filename as text)=>
let
Connection =srcDealers{idx}[Connection]

,sharepointfiles = SharePoint.Files("https://xxxxxxxx.sharepoint.com/sites/folder/", [ApiVersion = 15])
,filterRows = Table.SelectRows(sharepointfiles, each ([Folder Path] = "https://xxxxxxxx.sharepoint.com/sites/folder/Shared Documents/Scripts/" and Text.Contains(Text.Upper([Name]),Text.Upper(filename))))

,content = Text.FromBinary(filterRows[Content]{0})

,data=Sql.Database(Connection,"DBName",[Query=query])

in
data

[HoangV]

Unfortunately there's about more than 100 differnt ips. The workspace would separate them out, so I was hoping the function above would resolve the issue for us having to hard code each data flow into the workspace the data connection as some will have more than one in which case we'll have to udpate teh flow for those group. And potentially more will go online. 

If this is the limitation then there's nothing we can do about it. 

[lbendlin · Accepted solution]

You cannot dynamically swap the connection in a Sql.Database() source (or in any other source for that matter)

BUT.  If your databases all reside on the same server AND you have access to all the databases you can cheat by specifying a fixed database name in the connection and then override it in the query.