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

Aggregate column value based on category

I have this table:

snip1.PNG

 

And I want to create a new table with one column containing VPU names and two other columns containing the sum of actual runs for each VPU category and planned runs for each VPU category. Like this:snip2.PNG

I cant figure out how to use the column values in one table to create a new table. Please help me out. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Aggregate column value based on category

Then you can create a table in the Query Editor.  Copy the original table, and then apply this Group By transformation:

#"Grouped Rows" = Table.Group(#"<PREVIOUS STEP NAME>", {"VPU"}, {{"Planned Runs", each List.Sum([Planned Runs]), type number}, {"Actual Runs", each List.Sum([Actual Runs]), type number}})

And you'll have a second table aggregated by the VPU.  You can also use the Group By wizard in the Transform tab to do the same thing.

3 REPLIES 3
Super User
Super User

Re: Aggregate column value based on category

Are you trying to create a new data table in your model, or do you just want to display this info in a visual table?

 

I would suggest the latter, if that fits your criteria.  It would be as easy as dragging the fields into a table visual and making sure that Planned and Actual runs are aggregated as sums.

thesarim1 Frequent Visitor
Frequent Visitor

Re: Aggregate column value based on category

@Cmcmahan wrote:

Are you trying to create a new data table in your model, or do you just want to display this info in a visual table?

 

I would suggest the latter, if that fits your criteria.  It would be as easy as dragging the fields into a table visual and making sure that Planned and Actual runs are aggregated as sums.


I am trying to create a new data table and not a visual. I figured out how to create a visual, but I want it as a data table that has a relationship with the original table based on VPU categories. Thanks for your help though. Appreciate it.

Super User
Super User

Re: Aggregate column value based on category

Then you can create a table in the Query Editor.  Copy the original table, and then apply this Group By transformation:

#"Grouped Rows" = Table.Group(#"<PREVIOUS STEP NAME>", {"VPU"}, {{"Planned Runs", each List.Sum([Planned Runs]), type number}, {"Actual Runs", each List.Sum([Actual Runs]), type number}})

And you'll have a second table aggregated by the VPU.  You can also use the Group By wizard in the Transform tab to do the same thing.

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: 41 members 975 guests