0

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

  • found a solution e.e – Alan Aug 14 at 10:32
0

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: enter image description here

There you add following formula into the Text field:

=Excel.CurrentWorkbook()

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

enter image description here

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: enter image description here

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.