Forum Discussion
Dynamic data sources with a Sql.Database call
let
Source = Sql.Databases(".\sql2019"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ( [Name] = "Construct-a-Creature" or [Name] = "Create_A_Creature_02" or [Name] = "Movies2021")),
Rows = Table.AddColumn(#"Filtered Rows", "Return", each Value.NativeQuery(Source{[Name=[Name]]}[Data],"select * from product",null,[EnableFolding = true])),
#"Expanded Return" = Table.ExpandTableColumn(Rows, "Return", {"ProductId", "ProductName", "Animal", "HabitatId", "Legs", "FamilyId", "WeightGrams", "ProductionCost"}, {"Return.ProductId", "Return.ProductName", "Return.Animal", "Return.HabitatId", "Return.Legs", "Return.FamilyId", "Return.WeightGrams", "Return.ProductionCost"})
in #"Expanded Return"
This question is more about "combining the same table over multiple databases", not combining multiple tables in 1 database.
Doing that in a stored Procedure is far from ideal; the goal was to handle all "business insights" data collection outside of SQL, in Power BI. Is there any other way to iterate a (fixed) list of databases from Power BI?
Can you do this on yours?
Connect to the Server, filter to the databases, expand table names, filter to tablename, expand tables:
let
Source = Sql.Databases(".\sql2019"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Construct-a-Creature" or [Name] = "Create_A_Creature_02" or [Name] = "Movies2021")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Name", "Data"}, {"Name.1", "Data.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Kind"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Name.1] = "Product")),
#"Expanded Data.1" = Table.ExpandTableColumn(#"Filtered Rows1", "Data.1", {"ProductId", "ProductName", "Animal", "HabitatId", "Legs", "FamilyId", "WeightGrams", "ProductionCost", "Family", "Habitat", "Purchase", "Sales"}, {"ProductId", "ProductName", "Animal", "HabitatId", "Legs", "FamilyId", "WeightGrams", "ProductionCost", "Family", "Habitat", "Purchase", "Sales"})
in
#"Expanded Data.1"File attached.
Thanks Sam,
This is the first time I see the datasource getting successfully refreshed while dynamically iterating databases. (I guess not having the dynamic Sql.Database clears the "dynamic data source" check).
However, I need to be able to execute a specific SQL statement on those databases; is there any way to achieve that from the dynamically listed databases?
Thanks again!
Koenlet
Source = Sql.Databases(".\sql2019"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ( [Name] = "Construct-a-Creature" or [Name] = "Create_A_Creature_02" or [Name] = "Movies2021")),
Rows = Table.AddColumn(#"Filtered Rows", "Return", each Value.NativeQuery(Source{[Name=[Name]]}[Data],"select * from product",null,[EnableFolding = true])),
#"Expanded Return" = Table.ExpandTableColumn(Rows, "Return", {"ProductId", "ProductName", "Animal", "HabitatId", "Legs", "FamilyId", "WeightGrams", "ProductionCost"}, {"Return.ProductId", "Return.ProductName", "Return.Animal", "Return.HabitatId", "Return.Legs", "Return.FamilyId", "Return.WeightGrams", "Return.ProductionCost"})
in #"Expanded Return"