Forum Discussion
Power Query: If statement to alternate between datasources
I have several queries with multiple servers as data sources, and I would like to simplify the way I enable the connection with those servers by using parameters and IF statements. I don't have access to all servers right now, so I don't wanna waste time openning query by query to take off all /* */ when get access to others servers.
Here is sample of what I have now.
let
SourceARA = Sql.Database(ARA, Database, [Query=" "]),
#"ColSourceARA" = Table.AddColumn(SourceARA, "Server", each ARA),
/*
SourceGOI = Sql.Database(GOI, Database, [Query=" "]),
#"ColSourceGOI" = Table.AddColumn(SourceGOI, "Server", each GOI),
*/
SourceIBI = Sql.Database(IBI, Database, [Query=" "]),
#"ColSourceIBI" = Table.AddColumn(SourceIBI, "Server", each IBI),
#"CombinedTables" = Table.Combine({
#"ColSourceARA",
//#"ColSourceGOI",
#"ColSourceIBI"
})
in
#"CombinedTables"
I already tried to write a query, but I'm get stuck on how to put all the code (Source= and #ColSource=) inside the same if statement. Here is how I think it should looks like.
let
if ACT_SourceARA == 1 then (
SourceARA = Sql.Database(ARA, Database, [Query=" "]),
#"ColSourceARA" = Table.AddColumn(SourceARA, "Server", each ARA) )
else "" // Do nothing
,
if ACT_SourceGOI == 1 then (
SourceGOI = Sql.Database(GOI, Database, [Query=" "]),
#"ColSourceGOI" = Table.AddColumn(SourceGOI, "Server", each GOI) )
else "" // Do nothing
,
if ACT_SourceIBI == 1 then (
SourceIBI = Sql.Database(IBI, Database, [Query=" "]),
#"ColSourceIBI" = Table.AddColumn(SourceIBI, "Server", each IBI) )
else "" // Do nothing
,
#"CombinedTables" = Table.Combine({
if ACT_SourceARA == 1 then
"ColSourceARA"
else ""
,
if ACT_SourceGOI == 1 then
#"ColSourceGOI"
else ""
,
if ACT_SourceARA == 1 then
#"ColSourceIBI"
else ""
})
in
#"CombinedTables"
I appreciate any help.
- Anonymous4 years ago
HI Anonymous,
It seems like null cannot be used as the default value in the table.combine function.
Can you please share some dummy data about the result table? We can reference them to create a template table by the #table function.How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
3 Replies
- AnonymousNot applicable
HI Anonymous,
You can try to use the following code if helps:
let ACT_SourceARA = 1, ACT_SourceGOI = 1, ACT_SourceIBI = 1, fxConnection = (server as text) as table => Table.AddColumn( Sql.Database(server, "Database"), "Server", each server ), #"CombinedTables" = Table.Combine( { 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"Regards,
Xiaoxin Sheng
- AnonymousNot applicable
Thank you @v-shex-msft !
I think it would work, but I'm getting this error:
Expression.Error: We cannot convert the value null to Table. Details: Value= Type=[Type]Already tried replace null by number and text but it didn't work. How could I solve that?
- AnonymousNot applicable
HI Anonymous,
It seems like null cannot be used as the default value in the table.combine function.
Can you please share some dummy data about the result table? We can reference them to create a template table by the #table function.How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng