cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Believer
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
watkinnc
Solution Sage
Solution Sage

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

View solution in original post

6 REPLIES 6
watkinnc
Solution Sage
Solution Sage

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

View solution in original post

@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
Solution Sage
Solution Sage

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
Frequent Visitor

@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
Frequent Visitor

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...

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))

 

 

 

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors