How to Dynamically Append Tables in Power Query

When appending tables in Power Query, the M code used looks something like below:

=Table.Combine({Table1, Table2, Table3})

The problem here is if sometimes we only have Table 1, sometimes all three tables are available, it's not flexible to append these tables. For example, if we only have Table 1 and 2, we will get error like below which tells us that the table can’t be found.

This makes it difficult to append if the number of tables to be appended is always changing.

So here's the M code I used to handle the problem:

let
    Queries =
        {
            Table1,
            Table2,
            Table3,
        },

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

    Appended = Table.Combine(ErrorToEmpty),

    Result = if Table.IsEmpty(Appended) then "" else Appended

in

Result

Before using this code to dynamically append the tables, ensure that you have set a maximum possible number of tables to be append. Also ensure that you have created the query for each table, even if it's a blank table at the moment you write this code. So if at the moment you only have Table 1 and 2, just duplicate one of these query and rename them to get data from Table 3.

Code explanation:

The first part basically set the maximum possible number of tables to be appended, so here I have set 3 tables. So I put the 3 tables names in a list and named as "Queries".

let
    Queries =
        {
            Table1,
            Table2,
            Table3,
        },

Next part basically return the list in Queries itself if it’s not error, if it’s an error, it will return an empty list.

List.Position basically return all the position in the list, so it means transforming/return all list in Queries.

More explanation on List.Transform can refer the link here: https://powerquery.how/list-transform/

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

The last part combine/append all the non-empty tables in the previous steps which is the ErrorToEmpty step.

And If all tables are empty, it will return an empty table.

Appended = Table.Combine(ErrorToEmpty),

Result = if Table.IsEmpty(Appended) then "" else Appended

Finally, hope this short article can help you to gain some knowledge. Please like this post if you find it helpful~

And have a nice day and happy data analyzing!