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"
Super UserHi KoenJ
Have you tried using a power bi parameter for the [DatabaseName] then you could run the custom function as a new column within the table_TENANTS.
This wouldn't count as a dynamic data source.
I've mocked it up in the attached file
Super UserWorked example:
Thanks Sam,
Yes, that actually was my initial approach. Afterwards, I tried to make it "less dynamic" by removing the function call.
While your function runs fine locally, it does trigger the same error when it gets published to app.powerbi.com:
So, it sill looks at this as a dynamic data source?
Thanks again,
Koen- SamWiseOwl
Dang it. Seems like you can parameterise each bit of the single table and its fine.
As soon as the function is added it throws a wobbly.Could you create a stored procedure in SQL that combines all the tables?
Sadly you can't pass parameters into a stored procedure as that counts as dynamic string.
