Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Skip connection if server is down with Power Query

I'm trying to set parameters on PBI, and then check in a query if the related servers are on or not.

So I set 3 parameters to indicate the status of the servers:

ACT_SourceARA = 1
ACT_SourceGOI = 1
ACT_SourceIBI = 1

and 3 parameters with server address:

ARA = .....

GOI = .....

IBI = .....

Then I created a query with this code:

 

let
    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"

 

 I get stuck because when one of those parameters ACT_Source is equal to 0 (the server is not working and I want to skip connection), I get this error:

 

Expression.Error: We cannot convert the value null to Table.
Details:
Value=
Type=[Type]

 

What could I do to solve that?

Thank you!!

Need Help
Tips and Tricks
Tutorial Requests
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use below code

    let
    fxConnection =
        (server as text) as table =>
            Table.AddColumn(
                Sql.Database(server, "Database"),
                "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"

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Use below code

    let
    fxConnection =
        (server as text) as table =>
            Table.AddColumn(
                Sql.Database(server, "Database"),
                "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"
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much Vijay_A_Verma !!!

      It is a simple solution and works perfectly.

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    When the server is down, you are returning null, but inside a Table.Combine, it is expecting a table. 

    You would need to return a table there. Fill it with nulls for each column, but it must have the same columns as the others, otherwise it adds extra columns to your combined table.

    Then after the combine, filter out the null row(s).