Forum Discussion
Iterate Queries Based On Other Query
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?
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...
5 Replies
Anonymous ,
Creating a view querying these databases is not an option ?
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.
