Power Query: Combine Subset of List of Tables that Exist

Question

I am using Power Query in Excel for Microsoft 365.

I have a Power Query whose first step is to combine a set of tables:

let
    Source = Table.Combine({One_foo_bar, Two_foo_bar, Three_foo_bar, Four_foo_bar, Five_foo_bar, Six_foo_bar, Seven_foo_bar, Eight_foo_bar}),
    // ...
    #"Result" = ...
in
    #"Result"

The problem is that not all of the named tables may exist. Any subset of the tables may exist. Those that do exist are in ThisWorkbook. If any are missing, the M code shown above will throw an error.

If pattern matching can help solve this problem, the table names all follow the pattern *_foo_bar. M does not seem to have support for this type of pattern matching, but I include this statement in case anybody knows something I don't and could utilize this fact to formulate a solution.

How may I modify this code to combine the tables that do exist?

Answer 1

Use Excel.CurrentWorkbook() as the Source, then filter the Name column to return only the items that end with "_foo_bar". For example,

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.EndsWith([Name], "_foo_bar")),
    #"Combine Tables" = Table.Combine(#"Filtered Rows"[Content])
in
    #"Combine Tables"

Answer 2

Good question, and this is not a full answer, but I would start with this idea. First, build the array of existing tables in excel like:

then using power query to get the existing tables from that table.

An alternative is to build that array more directly in PowerQuery using TRY

Answer 3

Although I have accepted the answer given by @DjC, I'll post for future reference an answer I arrived at using the suggestion given by @gns100.

let
    remove_errors_f = (input_list as list) as list =>
                          List.RemoveNulls(
                                              List.Transform(
                                                                List.Positions(input_list),
                                                                each try input_list{_} otherwise null
                                                            )
                                          ),

    #"MyTables" = remove_errors_f({One_foo_bar, Two_foo_bar, Three_foo_bar, Four_foo_bar, Five_foo_bar, Six_foo_bar, Seven_foo_bar, Eight_foo_bar}),
    #"Column Names" = {"Column1", "Column2", "Column3", "Column4"},

    combiner_f = (AccumulatedTable as table, TablesToCombine as list) as table =>
                 if List.Count(TablesToCombine) = 0 then
                     AccumulatedTable
                 else
                     @combiner_f(Table.Combine({AccumulatedTable, TablesToCombine{0}}), List.RemoveFirstN(TablesToCombine, 1)),

    Source = combiner_f(#table(#"Column Names", {}), #"MyTables"),
    // ...
    #"Result" = ...
in
    #"Result"