How to add two tables based on same columns

[markus_zhang]

Hi experts,

This is an Excel 2016 case. I'm using Power Query to import two tables, after mashing up they have the following structure:

Main table:

Spot ID | Product A Impressions | Product B Impressions | ... | (So on may have 20-30 products)

Secondary table:

Spot ID | Product E Impressions | Product K Impressions

Question:

All spots in secondary table (about 50-75) are in main table (about 2,000+), is it possible to use Power Query or Power Pivot to add the impressions in secondary table to the same columns of main table?

For example if in secondary table we have a spot id 12345 having impression 100 for Product E, and in the main table the same spot has 900 for same product, now I want to create a new table, with the same structure of the Main table (and have all spots present), but have 1,000 impressions for 12345 on Product E.

Thank you!

[markus_zhang]

Final Solution:

For anyone who might find this post useful, in the future, I'll provide the final solution.

Instead of suing Power Query, I use Python + pandas to solve the problem, the line of code that really handles the job looks like this:

Result = Result.groupby('spot id', sort = None).sum().reset_index()

Basically Result (before this line) is the appended version, and then groupby().sum() will add up based on same value of [spot id].

[v-jiascu-msft]

Thanks for sharing, @markus_zhang.

Best Regards,
Dale

[v-jiascu-msft · Accepted solution]

Hi @markus_zhang,

We can do it with some built-in functions. Please refer to the snapshot below and the demo in the attachment. 

1. Append them together. 

2. Group by the [Spot ID].

Best Regards,
Dale

[markus_zhang]

Thanks Dale, will accept as solution.

It seems that GROUP BY will automatically remove any column that is not in the list, so this poses one problem - I have about 200+ columns so it's kind of clumsy to add all of them into GROUP BY. 

Second question, is it possible to directly GROUP on the same column instead of creating new ones?

Thank you!