Custom Function to run a SQL query a list of servers

[volfied]

I'm wondering if I'm trying to be too cute here.

I've built a custom function that accepts three parameters (a server name and a couple arguments) and runs a SQL query against the server passed in via the first parameter. I then import a table of four server names and invoke the custom function on each row to pull in the data for each server. The first row expands fine, but the subsequent rows all return empty tables when I know they shouldn't. Obviously, I could do each of these as hard-coded separate queries, but it feels like this should work. Any thoughts on what I might be doing wrong?

let data = (prmServer, prmPromos, prmDateFrom) =>

let
    Source = Sql.Database(prmServer, "cis", [Query="<SQL QUERY GOES HERE>")
in
    Source

in data

let
    PromoSource = Excel.CurrentWorkbook(){[Name="tblPromos"]}[Content],
    PromoDelimit = Table.TransformColumns(PromoSource,{{"Promos", each Text.Combine({"'",(Text.Combine({(_),"'"}))})}}),
    PromoCombine = Text.Combine(Table.ToList(PromoDelimit), ","),
    DateSource = Excel.CurrentWorkbook(){[Name="tblDateFrom"]}[Content],
    #"Extracted Date" = Table.TransformColumns(DateSource,{{"DateFrom", DateTime.Date, type date}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Date",{{"DateFrom", type text}}),
    DateDelimit = Table.TransformColumns(#"Changed Type",{{"DateFrom", each Text.Combine({"'",(Text.Combine({(_),"'"}))})}}),
    DateCombine = Text.Combine(Table.ToList(DateDelimit), ","),
    Source = Excel.CurrentWorkbook(){[Name="tblServers"]}[Content],
    addPromos = Table.AddColumn(Source, "Promos", each PromoCombine),
    addDateFrom = Table.AddColumn(addPromos, "DateFrom", each DateCombine),
    #"Invoked Custom Function" = Table.AddColumn(addDateFrom, "Data", each qryProgs([server], [Promos], [DateFrom])),
    #"Expanded Data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Data", {<COLUMN NAMES GO HERE>})
in
    #"Expanded Data"

[smpa01 · Accepted solution]

@volfied  as far as I know, you can't dynamically pass on a server name and DB name with a parameter. I experimented with this and go thte following error

Some data sources may not be listed because of hand-authored queries

[volfied]

Bummer. It would have been so elegant. Back to the drawing board. Thank you!