Putting this here for future reference...
I got some really cool code from GPT 4.1 which read my list of servers and databases from a SQL table, looped through them to combine all the tables. Also got a version which would let me use custom SQL to pull from each one instead of just a table. It all worked great in dataflow design view, but the dataflow would not save. It gave me the following error: "One or more tables references a dynamic data source." On researching this I realized there's probably no way around it, or at least no easy way, because... every time you add a new connection to a dataflow you also have to add the connection credentials (in my case a gateway connection). Even if the code would dynamically connect to a new server/database, the refresh would fail for lack of having the credentials.
For posterity's sake I am going to post the two versions of the code from GPT. (I don't have the actual working versions any more because I saved them in a comment block before the "let" statement of my old code and when I went back to retrieve it I discovered the DF dropped all the code. Argh!)
Here's the GPT code for dynamically aggregating a table across servers. I scrubbed the code of real server, database, table column names so hopefully I didn't jack it up too bad 😂
let
DBConnectionQuery = "
select [ServerName], [Database Name]
from [ServersAndDatbases]
",
DBConnections = Sql.Database(
"<SQL server name>",
"<SQL database name>",
[Query = DBConnectionQuery]
),
CustomSQL = "
Select * from tblYourTable
Where ...
);
",
ConnectionRows = Table.ToRecords(DBConnections),
GetTable = (r as record) =>
let
server = r[ServerName],
database = r[DatabaseName],
result = Sql.Database(server, database, [Query = CustomSQL])
in
result,
YourTables = List.Transform(ConnectionRows, GetTable),
Combined = Table.Combine(YourTables)
in
Combined
Here's the version for aggregating a custom SQL statement across servers:
let
// 1. Get the list of server/database pairs dynamically
DBConnectionQuery = "
select [ServerName], [Database Name]
from [ServersAndDatbases]
",
Connections = Sql.Database(
"<SQL server name>",
"<SQL database name>",
[Query = DBConnectionQuery]
),
// 2. Set the table name and schema
TableName = "tblYourTable",
SchemaName = "dbo",
// 3. Convert to records for easier iteration
ConnectionRows = Table.ToRecords(Connections),
// 4. Function to get the required table for one connection
GetTable = (r as record) =>
let
server = r[Server Name],
database = r[Database Name],
allTables = Sql.Database(server, database),
tableRow = Table.SelectRows(
allTables,
each [Schema] = SchemaName and [Item] = TableName
),
table = if Table.RowCount(tableRow) > 0
then tableRow{0}[Data]
else null // handle missing tables gracefully
in
table,
// 5. Get all tables (some could be null if not present)
AllTables = List.Transform(ConnectionRows, GetTable),
// 6. Remove any nulls in case some tables do not exist
PresentTables = List.RemoveNulls(AllTables),
// 7. Combine into one table
Combined = Table.Combine(PresentTables)
in
Combined
