Append all queries that have no error

[Luggruff]

Hi!

I have about 10 queries that may result in nothing, as the sources may be blank. I still need to combine them into a master table.

When trying Table.Combine in a blank query, I only get an error even if one of the 10 tables have something, as the rest of them is throwing an error regarding not finding the first column from the source.

Since I am doing this in Excel, it will throw an error either way if the appended query has no data too, so I created a fallback query with the same structure and some dummy data that will not be able to relate to anything else, that I want to use if ALL the 10 queries are returning nothing, so there will be no error on the refresh in Excel.

I tried applying [Append Query (does not work when primary query is empty)] (https://community.powerbi.com/t5/Power-Q... but it only applies when there are two tables. What I need is to tell Power Query to:

"Return data from query A, B, C ... X, Y and Z, for those that do not generate an error, and if all generate an error, return the fallback query".

So this won't work for example:

= try Table.Combine({#"Cancelled pickups Query 1", #"Cancelled Query 2", #"Cancelled pickups Query 3", #"Cancelled pickups Query 4", #"Cancelled pickups Query 5", #"Cancelled pickups Query 6", #"Cancelled pickups Query 7", #"Cancelled pickups Query 8", #"Cancelled pickups Query 9", #"Cancelled pickups Query 10"}) otherwise #"Cancelled pickups fallback"

Any idea how I might achieve only appending the queries that have no error, or if all have errors, load the fallback query?

[AlexisOlson · Accepted solution]

I'd return empty tables for queries with errors and then check if the total combination is empty like this:

let
    Appended =
        Table.Combine(
            {
                try #"Cancelled Query 1" otherwise #table({},{}),
                try #"Cancelled Query 2" otherwise #table({},{}),
                try #"Cancelled Query 3" otherwise #table({},{}),
                try #"Cancelled Query 4" otherwise #table({},{}),
                try #"Cancelled Query 5" otherwise #table({},{}),
                try #"Cancelled Query 6" otherwise #table({},{}),
                try #"Cancelled Query 7" otherwise #table({},{}),
                try #"Cancelled Query 8" otherwise #table({},{}),
                try #"Cancelled Query 9" otherwise #table({},{}),
                try #"Cancelled Query 10" otherwise #table({},{})
            }
        ),
    Result = if Table.IsEmpty(Appended) then #"Cancelled Fallback" else Appended
in
    Result

To prevent a bunch of repeated typing, we can apply try ... otherwise to the list of tables like this:

let
    Queries =
        {
            #"Cancelled pickups Query 1",
            #"Cancelled pickups Query 2",
            #"Cancelled pickups Query 3",
            #"Cancelled pickups Query 4",
            #"Cancelled pickups Query 5",
            #"Cancelled pickups Query 6",
            #"Cancelled pickups Query 7",
            #"Cancelled pickups Query 8",
            #"Cancelled pickups Query 9",
            #"Cancelled pickups Query 10"
        },
    ErrorToEmpty = List.Transform(List.Positions(Queries), each try Queries{_} otherwise #table({}, {})),
    Appended = Table.Combine(ErrorToEmpty),
    Result = if Table.IsEmpty(Appended) then #"Cancelled Fallback" else Appended
in
    Result

Note: I first tried this with a simpler ErrorToEmpty step like this

List.Transform(Queries, each try _ otherwise #table({},{}))

 For reasons discussed here, this doesn't work. So I used what @OwenAuger suggested here instead:

https://community.powerbi.com/t5/Desktop/Removing-errors-from-list-not-column-in-Power-Query-M/m-p/7...

[ImkeF]

Hi @Luggruff ,
you would have to apply the try .. otherwise to every single table like so:

try Table.Combine({
try #"Cancelled pickups Query 1" otherwise #"Cancelled pickups fallback",
try #"Cancelled Query 2" otherwise #"Cancelled pickups fallback",
...
#"Cancelled pickups Query 3", #"Cancelled pickups Query 4", #"Cancelled pickups Query 5", #"Cancelled pickups Query 6", #"Cancelled pickups Query 7", #"Cancelled pickups Query 8", #"Cancelled pickups Query 9", #"Cancelled pickups Query 10"}) otherwise #"Cancelled pickups fallback"