Iterate Queries Based On Other Query

[Anonymous]

Hello,

I am creating a Power BI query for a system running on on-prem SQL Server (2016).  Basically, the system has multipe departments that has its own database with identical schema. I need to create a query that runs through all departments (databases) and get rows from a table (office).  So I was able to get the list of departments (databases) with the following:

let
    Source = Sql.Database("dbserver01/db01", "master", [Query="SELECT name FROM sys.databases"])

But now I need to loop through this result and query other databases in the following manner:

<some looping with results above>
    let
        offices = Sql.Database("dbserver01/db01", Source[name], [Query="SELECT officeName FROM Offices"]

Is there a way to loop through Source and create sub-queries for each databases and return the result of combined officeName across the databases?

[camargos88]

@Anonymous ,

Creating a view querying these databases is not an option ?

[Anonymous]

Technically, I could hardcode all the databases and append queries, but we have over 30 departments and needs to account for new departments being added. It would be ideal to do it programmatically if possible.

[camargos88]

@Anonymous ,

You can create a dynamic select inside a procedure to execute it.

Also it's possible to do it using Power Query by creating a function to select in each database.

I'm gonna work on an example here.

[Anonymous]

Seriously, Microsoft, after taking a long time and great effort to type a painfully long solution to my question, and your forum software just ditches the work and tells me I am not signed in?

[Anonymous · Accepted solution]

Anyways, the solution is to create the query with parameter as the database name and return the office names for a given department. Then you can convert this to a function with the parameters getOffices(DepartmentName).

Then create another query to the master database to get the department name and use Table.TransformRows(Departments, each getOffices(_[dbName])) then convert the resulting list to Table.  

This is much shorter version of what I've originally typed. Kinda not sure if I should mark this as the answer...