Power Query: If statement to alternate between datasources

[Anonymous]

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.

[v-shex-msft]

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

[Anonymous]

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?

[v-shex-msft · Accepted solution]

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  

Chris Webb's BI Blog: Creating Tables In Power BI/Power Query M Code Using #table() Chris Webb's BI Blog (crossjoin.co.uk)

Regards,

Xiaoxin Sheng