Excel - Pivot table from multiple Tables with the exact same columns

So I've got Datasets from multiple third party sources, which have the exact same layout.

I put them into excel into 2 Tables like this:

Table 1 from source1
Year Month InfoType Source Cost
2019 01 Type1 source1 20
...

Source beeing a formula that looks like this: ="source1"

Table 2 from source2
Year Month InfoType Source Cost
2019 01 TypeD2 source2 10
...

Source beeing a formula that looks like this: ="source2"

and i want to combine both tables into a pivot table like this:

Rows Sum of Cost
2019 source1 20 source2 10
Total 2019 30

I know that the easiest solution would be to manually append the data, but i need the data to be split up in multiple tables in this case.

I've looked into relationships, but it seems like they behave similar to SQL Join, which is not what i am trying to accomplish here. I started looking into power pivot, but could not find anything that helps with my goal here.

i would appreciate any help. ty

1 Answer

The above idea can be accomplished using Power Query.

First you need to create 2 Table objects using your Data, by selecting your data and clicking on "format as table" or using the shortcut CTRL+T

Then you can open an empty Query, by clicking on "Data" then on "Retrieve Data", then on "Different sources" and then on "Empty Query"

This should open up the following window:

There you add following formula into the Text field:

=Excel.CurrentWorkbook()

After pressing enter, you should be seeing something like this:

There you can click on the button next to "Content" and uncheck the checkbox at the bottom, then clicking on OK

Now you should be seeing a table:

This is in fact the combined table of all Tables in your workbook. so if you happen to add another table with the same layout, it will be automatically appended into this table.

So now you can click on the dropdown under "close and load", then click on "close and load in..."

you should be seeing a popup, with 4 options:

Table
PivotTable-Report
Pivot Chart
Connection only

There you can choose PivotTable-Report and click on OK.

This should create a PivotTable. There you can add in your fields that you want to have displayed and you should be ending up with something like this: