Can these combined tables be put into a single table definition?

[Believer]

I have a lot of dataflows where I am combining 5 tables from different SQL servers into a single Power BI table. I am marking the individual tables as Enable Load = False in order to eliminate storing all the data twice, once in the original table and a second time in the combined table. Instead of having 6 table definitions (5 original sources plus 1 combined table) is it possible to put all of this into a single table definition/script?

My current table definitions look like the following, where the actual server and database names have been obfuscated with “<token>”. Note that the only differences are the Power BI table name (MytableX) and the SQL server name (SQL Server X).

Power BI table “MyTable1”
let
Source = Sql.Database("<Sql Server 1>", "<MyDatabase>"),
#"Navigation 1" = Source{[Schema = "dbo", Item = "MyTable"]}[Data]
in
#"Navigation 1"

Power BI table “MyTable2”
let
Source = Sql.Database("<Sql Server 2>", "<MyDatabase>"),
#"Navigation 1" = Source{[Schema = "dbo", Item = "MyTable"]}[Data]
in
#"Navigation 1"

…etc.

The final table definition, which is the only one where Enable Load = True, and therefore the only visible table in the dataflow looks like this:

Power BI table “MyTable”
let
Source = Table.Combine({MyTable1, MyTable2, MyTable3, MyTable4, MyTable5})
in
Source

[watkinnc · Accepted solution]

This works.  

let
Source = Table.Combine({
Table.SelectRows(Sql.Database("server_1", "databaseName"), each [Item] = "MyTable"){[Schema="dbo",Item="MyTable"]}[Data],
Table.SelectRows(Sql.Database("server_2", "databaseName"), each [Item] = "MyTable"){[Schema="dbo",Item="MyTable"]}[Data],
Table.SelectRows(Sql.Database("server_3", "databaseName"), each [Item] = "MyTable"){[Schema="dbo",Item="MyTable"]}[Data],
Table.SelectRows(Sql.Database("server_4", "databaseName"), each [Item] = "MyTable"){[Schema="dbo",Item="MyTable"]}[Data],
Table.SelectRows(Sql.Database("server_5", "databaseName"), each [Item] = "MyTable"){[Schema="dbo",Item="MyTable"]}[Data]})
in
Source

Replace "server_1", "server_2" etc. with your actual server names, and replace "databaseName" with the real database name.

--Nate

[Believer]

@watkinnc Thank you, this worked perfectly and it also refreshes very quickly!

@Vera_33 I still appreciate that your code is a bit more elegant, but I haven't been able to get past the parameter value prompt I keep getting.  Sometimes simpler is just easier 😉

[Vera_33]

Hi @Believer 

yes, you can do that, no syntax error, but you need to consider performance

let

t1 = let
Source = Sql.Database("<Sql Server 1>", "<MyDatabase>"),
#"Navigation 1" = Source{[Schema = "dbo", Item = "MyTable"]}[Data]
in
#"Navigation 1",

.
.
.
tn = let
Source = Sql.Database("<Sql Server n>", "<MyDatabase>"),
#"Navigation 1" = Source{[Schema = "dbo", Item = "MyTable"]}[Data]
in
#"Navigation 1"

in
Table.Combine({t1,..., tn})

and only difference is server? no difference here Schema = "dbo", Item = "MyTable"? then 

let 
 
 serverList = {"<Sql Server 1>","<Sql Server 2>",...,"<Sql Server n>"},
 fxReadData  = (x as text) => let 
        let
    Source = Sql.Database(x, "<MyDatabase>"),
    #"Navigation 1" = Source{[Schema = "dbo", Item = "MyTable"]}[Data]
        in
    #"Navigation 1"
in
Table.Combine(List.Transform(serverList,fxReadData))

[Believer]

@Vera_33 

I am trying to use your code and I am running into a couple errors.  Sorry, I have a long technical background but am relatively new to Power Query.

The first error I got was "Token Identifier expected" and it highlighted the space between the two consecutive "let" statements.  I removed one of them thinking it might be a duplicate; it saved, but back on the main screen I am being prompted for a value for the parameter "x".  I changed "Table.Combine(List.Transform(serverList,fxReadData))" to "Table.Combine(List.Transform(fxReadData(serverList)))".  Now I have other errors 🙄.  Here's my (redacted) code:

let
serverList = {"<Sql Server 1>","<Sql Server 2>","<Sql Server 3>","<Sql Server 4>","<Sql Server 5>"},
fxReadData = (x as text) => 
let
Source = Sql.Database(x, "MyDatabase"),
#"Navigation 1" = Source{[Schema = "dbo", Item = "MyTable"]}[Data]
in
#"Navigation 1",
Custom = fxReadData
in
Table.Combine(List.Transform(fxReadData(serverList)))

[Believer]

Thank you very much for the code!  The things I tried previously did not work.  I will use this and see what happens... I especially like the elegance of the loop 🙂.  Correct, the schemas are all identical.

Can you elaborate on "consider performance"... what is it about this solution which is not as performant, and how significant is it?  On my end I can also copy/re-engineer everything I have and compare the old/new refresh times...

[Vera_33]

Hi @Believer 

M is in let...in... structure, every step can be output like table, list, function, parameter, etc...when your current step is to transform a previous table, then call it.  The last step before in needs to be without comma, others need comma at the end.

The fxReadData is a function here (just convert your previous query/steps to a function), x is referring to server name (you can name it more meaningful), then in the List.Transform you call it to read each server name in previous serverList, you can have a look at M official doc though some of them are not so helpful...

https://docs.microsoft.com/en-us/powerquery-m/list-transform

Performance wise, I am not sure, you can compare...and format it here to improve readability:

https://www.powerqueryformatter.com/formatter

let
  serverList = {
    "<Sql Server 1>",
    "<Sql Server 2>",
    "<Sql Server 3>",
    "<Sql Server 4>",
    "<Sql Server 5>"
  },
  fxReadData = (x as text) =>
    let
      Source          = Sql.Database(x, "MyDatabase"),
      #"Navigation 1" = Source{[Schema = "dbo", Item = "MyTable"]}[Data]
    in
      #"Navigation 1"
in
  Table.Combine(List.Transform(serverList, fxReadData))