Forum Discussion
Programmatic Data Sources?
Assuming a few things like:
- All databases come from the same server
- The same authentication / credentials used has access to all databases
- You have a logic in place to determine what tables to pick from each database correctly
You can use the SQL Database connector, enter just the server name and that will yield a table with all the available databases inside of the server. From there, you can create your own query with the custom logic of your own or even package your logic as a function that could run against every single database in that server and yield a single query that you could later sink / output / load to a destination of your choice.
The article below might be able to help you with how to create functions:
Using custom functions in Power Query - Power Query | Microsoft Learn
For the SQL Database connector, you can also simply use the function Sql.Databases:
Assuming a few things like:
- All databases come from the same server
- The same authentication / credentials used has access to all databases
- You have a logic in place to determine what tables to pick from each database correctly
You can use the SQL Database connector, enter just the server name and that will yield a table with all the available databases inside of the server. From there, you can create your own query with the custom logic of your own or even package your logic as a function that could run against every single database in that server and yield a single query that you could later sink / output / load to a destination of your choice.
The article below might be able to help you with how to create functions:
Using custom functions in Power Query - Power Query | Microsoft Learn
For the SQL Database connector, you can also simply use the function Sql.Databases:
Ahh! That's really good, I totally missed that I could just grab everything into one super query and then split off from it as needed. That sounds very doable and I'll give it a try asap. Thank you!