cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Total production pero year and product in one table

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 1PRODUCT 2PRODUCT 3 TOTALTOTAL PRODUCTION 1TOTAL PRODUCTION 2TOTAL PRODUCTION 3
2019A50798100 2019208829233
2020A605047 2020176271292
2021A70089 202112527118
2019B8010123     
2020B100123156     
2021B451514     
2019C782110     
2020C169889     
2021C101215     
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Total production pero year and product in one table

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

Unpivot and Groupby.png

edhans New Contributor
New Contributor

Re: Total production pero year and product in one table

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:

 

20190815 10_47_48-.png

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

2 REPLIES 2
Super User
Super User

Re: Total production pero year and product in one table

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

Unpivot and Groupby.png

edhans New Contributor
New Contributor

Re: Total production pero year and product in one table

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:

 

20190815 10_47_48-.png

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 39 members 1,093 guests
Please welcome our newest community members: