Forum Discussion
Iterate SQL query based on list of sources in another table
Hi,
I'm executing identical SELECT statements from different servers. I'm using the approach below. But as you can see it's a problem if number of servers go up.
My current solution for n number of sources:
dots signify repetitions till number n
let
Source1= "xxx",
Source2= "yyy",
.
.
.
Sourcen= "zzz",
Query1="
SELECT * FROM Database_" & Source1 & ".dbo.Table
",
Query2="
SELECT * FROM Database_" & Source2 & ".dbo.Table
",
.
.
.
Queryn="
SELECT * FROM Database_" & Sourcen & ".dbo.Table
",
Source1Data = Sql.Database("" & Source1 & "vmsxxx", "Database_" & Source1 & "", [Query=""& Query1 &""]),
Source2Data = Sql.Database("" & Source2 & "vmsxxx", "Database_" & Source2 & "", [Query=""& Query2 &""]),
.
.
SourcenData = Sql.Database("" & Sourcen & "vmsxxx", "Database_" & Sourcen & "", [Query=""& Queryn &""]),
CombinedData = Table.Combine({#"Source1Data", #"Source2Data", ... #"SourcenData"})
in
CombinedData
What I want to achieve is this:
I want to write the query only once. Then using Power Query, I'd like to start an iteration based on another table in PQ which will contain list of "Sources".
Example source table:
| Source |
| Source1 |
| Source2 |
| Sourcen |
Something like this:
let
Sources = THE SOURCE TABLE
FOR EACH ROW IN SOURCES TABLE EXECUTE ONCE AND APPEND
Query="
SELECT * FROM Database_" rows in sources table ".dbo.Table
",
SourceData = Sql.Database("" & rows in sources table & "vmsxxx", "Database_" & rows in sources table& "", [Query=""& Query &""]),
FINAL STATEMENT MUST APPEND RESULT OF EACH ITERATION
CombinedData = Table.Combine({#"ITERATION SourceData 1", #"ITERATION SourceData 2", ... #"ITERATION SourceData N"})
in
CombinedData
Thank you for your help.
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.
8 Replies
Hi, your solution works as well but having the same problem, data sources are not visible as they are dynamic so I cannot use scheduled refresh. Do you know about this?
Thanks.
- KNP
Super User
Are you able to provide any more detail about the query/output you're trying to achieve?
Is any part of the server names consistent and known?
You may be able to do something with SQLCMD -L wrapped in a stored proc depending on your permissions.
CNENFRNLCommunity Champion
Simple 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 CombinedDataThe 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?
