Forum Discussion
Help with DW Schema queries
I'm working with multiple servers which have the same database, so I have to combine them. Sometimes they stop working, so I set parametes (ACT_Source...) to indicate if they are online or not.
The first query is currently working. I would like to change the second query (which has a different pattern) to have the same
I have two types of query and I would like to let them at the same patern.
let
fxConnection =
(server as text) as table =>
Table.AddColumn(
Sql.Database(server, Database, [Query="SELECT... "]),
"Server",
each server
),
#"CombinedTables" =
Table.Combine(List.RemoveNulls(
{
if ACT_SourceARA = 1 then
fxConnection(ARA)
else
null,
if ACT_SourceGOI = 1 then
fxConnection(GOI)
else
null,
if ACT_SourceIBI = 1 then
fxConnection(IBI)
else
null
})
)
in
#"CombinedTables"
This is second query:
let
Source = Sql.Database(server, Database),
DW_DEClass = Source{[Schema="DW",Item="DEClass"]}[Data]
in
DW_DEClass
I think it should looks like the query bellow. I tried add lines 2 and 3 from second query into the first query, but I don't know exactly what should be changed to make this work.
let
fxConnection =
(server as text) as table =>
Table.AddColumn(
( Source = Sql.Database(server, Database),
DW_DEClass = Source{[Schema="DW",Item="DEClass"]}[Data] )
"Server",
each server
),
#"CombinedTables" =
Table.Combine(List.RemoveNulls(
{
if ACT_SourceARA = 1 then
fxConnection(ARA)
else
null,
if ACT_SourceGOI = 1 then
fxConnection(GOI)
else
null,
if ACT_SourceIBI = 1 then
fxConnection(IBI)
else
null
})
)
in
#"CombinedTables"​
I appreciate any help!
Use this (I can't see you passing Database here. You might need to replace Database with something like "Database" here depending upon your database name)
let fxConnection = (server as text) as table => Table.AddColumn( Sql.Database(server, Database){[Schema="DW",Item="DEClass"]}[Data], "Server", each server ), #"CombinedTables" = Table.Combine(List.RemoveNulls( { if ACT_SourceARA = 1 then fxConnection(ARA) else null, if ACT_SourceGOI = 1 then fxConnection(GOI) else null, if ACT_SourceIBI = 1 then fxConnection(IBI) else null }) ) in #"CombinedTables"
2 Replies
- Vijay_A_VermaMost Valuable Professional
Use this (I can't see you passing Database here. You might need to replace Database with something like "Database" here depending upon your database name)
let fxConnection = (server as text) as table => Table.AddColumn( Sql.Database(server, Database){[Schema="DW",Item="DEClass"]}[Data], "Server", each server ), #"CombinedTables" = Table.Combine(List.RemoveNulls( { if ACT_SourceARA = 1 then fxConnection(ARA) else null, if ACT_SourceGOI = 1 then fxConnection(GOI) else null, if ACT_SourceIBI = 1 then fxConnection(IBI) else null }) ) in #"CombinedTables"- AnonymousNot applicable