Forum Discussion
Iterate SQL query based on list of sources in another table
I did a little more digging (and testing). Looks like you might be running into this issue.
https://aka.ms/dynamic-data-sources
The only way to get around this is to use parameters and as far as I know, you can't use dynamic parameters with a SQL data source. So you'd be back to square 1 with a solution that isn't dynamic having to define each source as a parameter.
You may be able to do something with dynamic SQL, unfortunately I don't have the time to try. To be honest, even if that worked you'd probably want to ask the question "should I?" for obvious reasons.
It looks like the 'E' in your ETL may need to be external to Power BI.
Community ChampionSimple enough, use List.Accumulate to iterate all sources in the source table,
let
QryFx = (db as text) =>
let
Sql = "SELECT * FROM Database_" & db & ".dbo.Table",
Dataset = Sql.Database("" & db & "vmsxxx", "Database_" & db & "", [Query=""& Sql &""])
in
Dataset,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4vLUpONVSK1YGxjZDYeUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source = _t]),
Iteration = List.Accumulate(Source[Source], {}, (s,c) => s & {QryFx(c)}),
CombinedData = Table.Combine(Iteration)
in
CombinedData
The solution works in Desktop but data sources are no longer detected from within the function as they are not explicit before its executed. Is there a way around this?
