Dynamic union queries based on list of DB names

[Shamatix]

Hi there,

So I have quite a lot of DBs with the same structure.

I would like to make a list of DBNames in a parameter

And then basically import data into 1 table using unions.

My question is the how I can smartly do the following

SELECT * FROM DBNAME2.dbo.TABLENAME

UNION ALL

SELECT * FROM DBNAME2.dbo.TABLENAME

Without having to type out all the DBNames, but just add em to the list?

So basically a loop doing unions based on the list of DB names

I hope my question makes sense.

Best regards

[v-yiruan-msft]

Hi @Shamatix ,

If I understand correctly, you would like to load the queries from multiple data sources dynamically. If yes, I'm afraid that it can't be achieved. If you only want to change the data source dynamically, you can refer the following blog to get it using Power Query parameter.

Change the Source of Power BI Datasets Dynamically Using Power Query Parameters

Best Regards

[Shamatix]

Hi there,

I found a way to loop over a list of database names and union the tables from the various databases into one table, all done dynamically 🙂

[v-yiruan-msft]

Hi @Shamatix ,

You are awesome!👍👍👍 If it is convenient, could you please share your solution(exclude the sensitive info) here? It will help the others a lot in the community when they face the similar requirement as yours. Thanks for your dedication and time. 

Best Regards

[Shamatix · Accepted solution]

Hi there,

Sure.

This can probably be done a bit cleaner than what I did.

But here you go (comments in the code).

let
    // Define a parameter named "DatabaseList" that contains a list of database names or have a single column table converted to a list.
    DatabaseList = Table.ToList(#"DatabaseTable"),

    // Define a function that takes a database name as input and returns a table named "Errors_Userdefined" with an extra column for the database name.
    GetErrors_Userdefined = (DatabaseName) =>
        let
            // Connect to the database.
            Source = Sql.Database(".", DatabaseName),
            // Try to select the "Errors_Userdefined" table from the database. If it exists, add a new column for the database name.
            // If it does not exist, return the null value.
            Errors_Userdefined = try Source{[Schema="dbo",Item="TABLENAMEHERE"]}[Data] otherwise null,
            
            AddedDatabaseName = if Errors_Userdefined <> null then Table.AddColumn(Errors_Userdefined, "DatabaseName", each DatabaseName) else null
        in
            AddedDatabaseName,

    // Call the "GetErrors_Userdefined" function for each database name in the "DatabaseList" parameter.
    Errors_UserdefinedList = List.Transform(DatabaseList, each GetErrors_Userdefined(_)),

    // Filter out the null tables from the "Errors_UserdefinedList".
    FilteredErrors_UserdefinedList = List.Select(Errors_UserdefinedList, each _ <> null),

    // Combine all tables in the "FilteredErrors_UserdefinedList" into one table using a union all.
    UnionAll = if FilteredErrors_UserdefinedList <> {} then Table.Combine(FilteredErrors_UserdefinedList) else null

in
    UnionAll

And I filter out the "null tables" as some of the databases in my databaselist may not have the table in the database.

So in the end I loop over a list of databasenames, and union a table from each database into one table in Power Bi dynamically 🙂

Feel free to ask questions

Best regards

[Shamatix]

Surely someone else must have had this issue before :S?