Hi @miguel , @Element115 , @v-cboorla-msft
I have built M code to produce the effect that I am trying to achieve as follows:
let
// Function to query top 100 rows from a table in a specific database
GetTableFromDatabase = (server as text, database as text) as any =>
let
Source = try Sql.Database(server, database) otherwise null,
MyTable = if Source <> null then Source{[Schema="dbo",Item="MyTable"]}[Data] else null,
Top100Rows = if MyTable <> null then Table.FirstN(MyTable, 100) else null
in
Top100Rows,
// Get list of databases from the SQL Server
GetDatabaseList = Sql.Databases("MyServer"),
DatabaseNames = Table.SelectColumns(GetDatabaseList,{"Name"}),
//Iterate through the list of databases, accumulating 100 rows of data from MyTable from each DB into a list
LoadDataFromAllDatabases = List.Accumulate(
DatabaseNames[Name],
{},
(state, currentDatabase) =>
let
DatabaseName = currentDatabase,
TableData = GetTableFromDatabase("MyServer", DatabaseName),
NewState = if TableData <> null then state & {TableData} else state
in
NewState
)
in
LoadDataFromAllDatabases
This is working for me up to a point, but I am having some issues with credentials which I am working on resolving with the relevant teams at my organisation.
So far, this approach seems to be viable and appears to be functioning fine in conjunction with the Gateway to the on-premises data.
Once I have the credential issues resolved, I will follow up with confirmation of success and/or any modifications required for the final solution.
Naturally, the code above has been masked and pruned to hide specific details for my implementation, but the code is generic and would work as a framework for any similar requirement.
Thanks for all of the assistance and guidance thus far.
😊
