How to combine tables, one of which is returning an error?

[PietroFarias]

Hello guys!

My help today is in the combination of 'Table.Combine' and 'try ... otherwise ...'.

I am performing a combination of tables in Power Query and one of the tables being combined comes from an "unstable" external connection. But I don't want to miss the combination because of this QUery. I would like to ignore this query in the combination if it represents an error. The code below is something I am trying to do. But as the combination needs to be of the TABLE type, so I got stuck in this part. I don't want to have to create an empty table using #table (), as this would leave the combination static.

Can someone help me?

let
    Source = 
    Table.Combine(
        {
            Query1,
            Query2,
            try Query3 otherwise null  //<<----
        }
    )
in
    Source

[v-yingjl]

Hi @PietroFarias ,

Maybe try to exchange the query order like this:

let
    Source = 
    Table.Combine(
        {
            try 
            Table.FindText()  //Query3 which represents an error
            otherwise
            Table.FromRecords({[CustomerID = 1, Name = "Bob"]}),  //Query1
            Table.FromRecords({[CustomerID = 2, Name = "Jim"]})   //Query2
        }
    )
in
    Source

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PietroFarias]

@v-yingjl,

In your example, if the first query does not generate an error, the Query1 will not be combined, it will come out of this combination.

[v-yingjl · Accepted solution]

Hi @PietroFarias ,

I have modified the query like this you can refer:

let
    Source =
    Table.Combine(
        List.RemoveNulls({
            Table.FromRecords({[CustomerID = 1, Name = "Bob"]}),  //Query 1
            Table.FromRecords({[CustomerID = 2, Name = "Jim"]}),  //Query 2
            try
            Table.FindText()  //Query 3 which represents error
            otherwise
            null,
            Table.FromRecords({[CustomerID = 3, Name = "AAA"]}),  // Query 4
            try
            Table.FirstValue()  //Query 5 which represents error
            otherwise
            null,
            Table.FromRecords({[CustomerID = 4, Name = "BBB"]})   // Query 6
        })
    )
in
    Source

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PietroFarias]

Great!!

Good ideia use List.RemoveNulls.

Thanks!

[amitchandak]

@PietroFarias , refer this there is an example how to handle error in M

https://www.poweredsolutions.co/2019/06/18/error-handling-iferror-in-power-bi-power-query/