How can I combine multiple tables that have the same Headings into one pivot table

Question

Hello, I have been trying to find an existing discussion to help me out but I'm struggling to find an exact example. I have a table for data that is entered each month. Each table has the exact same headers, but different info is filled in each month. I would like to see a summary for each month ( which I am easily able to do with a quick pivot table) each quarter, and then a yearly summary. Can I create pivot tables like the existing monthly pivot tables shown in the second photo for the quarter and year? I saw on another discussion that you can make Queries and then merge the two, but this looked like it would only be used when two tables weren't exactly the same and you just wanted to include another column from a separate table to your original table.

Answer 1

1. use Append.

2. add table

3. Here is the result.

Answer 2

Power Query is a simple way to accomplish that.

It is important that the headings relevant to your analysis are spelled the same in all tables.

It doesn't matter in which column the headings are, e.g. one table can have the "Date" in the first column and another can have the "Date" in the 3rd column, not problem at all. Power Query can append the data and sorts the columns automatically by the headings.

Also, it doesn't matter if some tables have some extra columns which are missing in others, it works.

If you need further help, please share your sample file.

Andreas.

Answer 3

'm not very familiar with Power Query. Can you walk me through a few steps? I tried following these instructions https://www.ablebits.com/office-addins-blog/excel-join-tables-power-query/ but I got stuck while trying to merge the tables.

Answer 4

Thanks so much! This worked.