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"
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!
Koen
Super Userlet
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"
Thanks, Sam.
I did not succeed to get it 100% working, because Power BI started complaining I needed to setup a Gateway. I am assuming that when it iterates too many databases, it requires the gateway.