Forum Discussion
Dataflow and PowerBI Gateway - Sourcing data dynamically
Hi all, I have a Dataflow Gen2 which is sourcing data from an on-premises SQL Server via PowerBI Gateway. The server contains many databases, each representing a retail POS. These databases may...
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.
😊
I don't think that there's a need to use Sql.Database when you're already querying and gaining access to all databases from the server with Sql.Databases. You're introducing the issue of unbinded connections when you're using new data source functions to the mix - if you just leverage the tree view given by Sql.Databases you should be able to achieve the same without incurring into accessing new data sources (and then have the credentials issue that you're seeing).