Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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.

 

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not 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?

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.