Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not 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