Merge and Summarize 2 Tables with 4 matching columns and 2 different columns (Image and file included).

[zapppsr]

Hi guys, I need your help:

Problem: I have 2 tables with 4 matching columns: [Ano, Mês, Unidade, CR].

But each table has na extra column with a different value: R$ and Produção.

In the example tables, they have the same quantity, but in real data I can have variations. Eg: CR 8001 can have 4 R$ and 7 Produção.

My goal is to create a third table, summarizing the data by [Ano, Mês, Unidade, CR] and having a column with the R$ and Produção Totals.


I need a third query in Power Query, not a manual table in Excel.

Consider using also Power Pivot. This merged and Summarized table will be the base for a Power Bi Report.

I tried to merge them but it doubled the 32 lines to 64. I don't know if I append them and treat them in Power Query I can have my desired result.

Please give me some hints... I'm sure you guys have a solution for this.

Here is the file link.

Thanks in advance.

 

[zapppsr]

Well, I appended the two tables, it doubled the lines as expected and one set of data have R$ empty and the other have Produção empty.
That was what to be expected, and when I create a pivot table I get my desired results.

But I don't know if that is the more efficient and elegant way to do it.
I still feel that a 32 lines with the R$ and Produção columns filled is the right way to go, especially in terms of performance.

 

[zapppsr]

Ok, now I used the Group By function and it worked like a charm.
Problem solved.