Skip to content

Append Multiple Tables in Power Query

Power Query transformations can be very powerful, but they only work on one data source at a time. Sometimes data providers will only provide their data in discrete chunks, like one category per table, or data may come from different providers with the same schema. Ultimately, we want to show these different sources together with different attributes, so that it may all be analyzed simultaneously. Power Query supports this requirement through its “Append” function.

Consider the following scenario. We want to analyze alcohol consumption data. The World Health Organization provides extensive data on this, but it is reported separately for each type of alcohol.


(source: Global Health Observatory Data Repository)

There is a source for total, but it does not break the consumption down by type. What we need to do is to append the four (beer, wine, spirits, other) categories together. To start with, we need to query for each type separately. The data is provided by the WHO as a CSV data file, but it’s directly downloadable, so we will use the “From Web” data source (which makes refresh simple and removes a download step). First we open up Excel, click the Power Query tab then click on the “From Web” external data source. We then enter the URL of our first category (beer) and click OK. The query editor window will then be opened.

We don’t need to do much in the way of transformation, just turn the first data row into headers (by clicking on the upper left grid icon). Then, we give the query a name (Beer), and importantly, we deselect the “Load to worksheet” load setting.


By default, the “Load to worksheet” option is selected (I’ve griped about this elsewhere), but in this case, we don’t want to load the data into the model OR the worksheet. Why not? We’re going to be using this query as an append source with other queries into a final all encompassing appended query, so there’s no point in incurring the data load or storage overhead of the extra data.

Once complete, we repeat this procedure for the other categories. Each of these queries have the same schema, so no transformations need to be made, but keep in mind that there may be cases where we need to do extra work to make sure that the schemas match. Once all of the category queries have been defined, we are ready to perform the append.

From the Power Query tab, we click on the “Append” button which allows us to select two tables.


This will create a new query with the result of the append operation. But wait a minute, we have four tables to merge, and the UI only gives us an option for two. We could append our two other tables together, create another append destination, and then append the two append results together, but that’s very cumbersome, and it certainly doesn’t scale much beyond 4 input sources. The ideal scenario would be to append all four sources in one step. Fortunately, that’s possible with Power Query – it’s just not obvious.

From the query that results from the initial append operation, we can see a formula in the formula editor – Table.Combine({Beer,Wine}).


This formula uses Power Query’s “M” language, and the good news is that not only can it be easily edited, the Table.Combine function takes more than 2 arguments. It’s a simple matter to add in our other queries to the function to get a single append function.


It should be noted that if the queries have a space in their name, it is necessary to refer to them as #”query name” – i.e. #”beer consumption”, etc.   At this point, we give the resultant query a name, and change the load options to load into the data model. Once loaded, we can import any other supporting data, enhance our model, and start analyzing.

This single append also demonstrates that whether or not a particular feature is supported through the user interface, It may be possible to accomplish the goal through some creative M language work. If you’re interested on some more things that can be done with M, I suggest you check out these examples on Chris Webb’s BI blog.


  1. david david

    Brilliantly simple. I would like to take this one step further. I have a table created by power query of all the tables in my excel file (one of the columns is the table names), i would like to pass all the rows of this table (basically the table names) to the append list so it will automatically add any new tables to the append query……..

    table list m “= Table.SelectRows(Source, each Text.StartsWith([Name], “p2014″))”

  2. […] document.write(''); Hi Dan, I'd recommend a different approach here that I've described in my latest blogpost: How to compare everything with everything in Power BI If you don't want to / are not able to take the "From-Folder"-method, you need to add the "Source"-column manually to all your tables during your load and perform an append-merge that UNIONs / creates the consolidated source-table: Append Multiple Tables in Power Query | The White Pages […]

  3. Sally Sally

    Hello. Thank you for the info. This is exactly what I need. Unfortunately I can’t find this functionality in Excel 2016. How can I find the function bar to make the necessary edits in 2016? Thank you

  4. Sally Sally

    Ah I think I found it – advanced editor then ->

    Source = Table.Combine({#”users BlackBurn”,#”users Blackpool”})

  5. Phil Barker Phil Barker

    Do you know of a way in which you can name the sheet where you load the table to based on values within the table e.g. first 3 chars of cell in column 2 row 1 and also the first 5 chars from column 3 row 1? Instead of Sheet 2

  6. Guy Manova Guy Manova

    that’s great man – been looking for this for a while. really great, thanks!

  7. This looks really promising.
    Is it possible to append an unknown amount of tables?
    We receive multiples files to process and in each of them there are 10s of tables in identical structure.
    At the moment my Query finds “Tabelle1” but the rest is forgotten.
    Many thanks in advance.


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.