Aggregate column value based on category

[Anonymous]

I have this table:

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:

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

[Cmcmahan]

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.

[Anonymous]

@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.

[Cmcmahan · Accepted solution]

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.