Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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))
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)))
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.