group by total in the same row in Power Query

[Anonymous]

Hello friends,

I have a table with three columns:

Employee, Project, Hours

showing hours spent by employees on different projects

I would like to add a column of TotalHours, summarizing total hours spent on all the projects for each employee.

I need this to calculate the percentage for each row.

Currently I am creating a new query grouped by empoyee with Sum of Hours and then Merge this query back into the detailed query.

It works fine but maybe there is a trick to do this without creating a separate query?

Thank you

Michael

[Eric_Zhang]

---

@Anonymous wrote:

Hello friends,

I have a table with three columns:

Employee, Project, Hours

showing hours spent by employees on different projects

I would like to add a column of TotalHours, summarizing total hours spent on all the projects for each employee.

I need this to calculate the percentage for each row.

Currently I am creating a new query grouped by empoyee with Sum of Hours and then Merge this query back into the detailed query.

It works fine but maybe there is a trick to do this without creating a separate query?

Thank you

Michael

---

@Anonymous

You may not have to calculate the precentage in advance. Try to use a Matrix visual as below.

[Anonymous]

Thanks Eric, that's a nice option of a Matrix visual.
Nevertheless I am looking for Power Query solution
I will use this calculation for further Power Query calculations and manipulations so DAX/visual level solutions do not apply.
Thanks
Michael

[MarcelBeug · Accepted solution]

You can embed your grouping in the main query:

let
    Source = HoursSpentTable,
    Grouped = Table.Group(Source, {"Employee"}, {{"TotalHours", each List.Sum([Hours]), type number}}),
    Merged = Table.NestedJoin(Source,{"Employee"},Grouped,{"Employee"},"NewColumn",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "NewColumn", {"TotalHours"}, {"TotalHours"})
in
    Expanded

Alternative:

let
    Source = HoursSpentTable,
    #"Added Custom" = Table.AddColumn(Source, "TotalHours", (This) => List.Sum(Table.SelectRows(Source,each [Employee] = This[Employee])[Hours]))
in
    #"Added Custom"

The alternative looks nicer, but it might well be that the first solution is faster, which would only be relevant for a rather large dataset.

[Anonymous]

Hi @MarcelBeug 

Would you care to elaborate how this embedding into main query works to a Power Query novice ?

I have an existing DataLoad stage and then referenced that stage as the source in a modelling query, where I basically want to perform the same exercise as the original problem in this post?

From testing I can't seem to figure out how to apply the "embed" the code into the existing query.

[MarcelBeug]

If you share the (relevant part of the) code from your efforts so far, I can probably point you to the required adjustments.

[Anonymous]

Awesome, thanks @MarcelBeug!

I thought you can only Merge/Join different Queries, not "steps/tables" inside one Query.

I only had a "feeling" it is possible....

Would you recommend some source with more advanced tricks like this for me to learn from?

Thanks a lot!

Michael