Dynamically combine SQL tables from multiple servers and databases

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