Total production pero year and product in one table

[OscarSuarez10]

Hello How can I use power query to sum the total production per year AND product including the production of each prouduct in only one table , as in the right table ?

YEAR		PRODUCT 1	PRODUCT 2	PRODUCT 3		TOTAL	TOTAL PRODUCTION 1	TOTAL PRODUCTION 2	TOTAL PRODUCTION 3
2019	A	50	798	100		2019	208	829	233
2020	A	60	50	47		2020	176	271	292
2021	A	70	0	89		2021	125	27	118
2019	B	80	10	123
2020	B	100	123	156
2021	B	45	15	14
2019	C	78	21	10
2020	C	16	98	89
2021	C	10	12	15

[Anonymous · Accepted solution]

Power Query and DAX are set up to work best with data in tabular format.  After you load your table, select Year and then Unpivot Other Columns.  Then from there you can GROUPBy Year and Attribute, with a new Column for SUM.  This will still have the table in tabular format, but then you can use a matrix, table, pivot table to get the output how you want to see it

. 

[edhans · Accepted solution]

Because you already have the columns like you want, just use GroupBy in the Transform ribbon of Power Query, select Advanced, then set it up like this:

strictly speaking that will give you want you want, but @Anonymous is right, you are better off going through the extra steps to have your data in a normalized table.

[Anonymous · Accepted solution]

Power Query and DAX are set up to work best with data in tabular format.  After you load your table, select Year and then Unpivot Other Columns.  Then from there you can GROUPBy Year and Attribute, with a new Column for SUM.  This will still have the table in tabular format, but then you can use a matrix, table, pivot table to get the output how you want to see it

.