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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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!!

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

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
Not applicable

Thank you so much @Vijay_A_Verma !!!

It is a simple solution and works perfectly.

edhans
Super User
Super User

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).



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors