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