Combining two rows with same ID, but choose the best value of other columns

[Anonymous]

Hi! 

I am having a problem with combining rows. My table contains information about employees in my company and in which department they work. Some employees work in multiple departments and therefore they show multiple times in my table. I want to summarize how many % they work across all departments and choose to show the department where they have the highest work % as their deparment. Is this possible?

The table looks like this:

Employee ID	Department	Name	Occupation	Work %
1	Technical	Bob	Engineer	100
2	Service	Todd	Sales	20
2	Service	Todd	Support	30
2	Technical	Todd	Supervisor	50
3	HR	Fred	HR-manager	100

And what I want to end up with should look like this:

Employee ID	Department	Name	Occupation	Work %
1	Technical	Bob	Engineer	100
2	Technical	Todd	Supervisor	100
3	HR	Fred	HR-manager	100

I have tried using the command 
= Table.Group(#"Renamed Columns", {"Name", "Employee ID", "Department", "Occupation"}, {{"Work %", each List.Sum([Work %]), type nullable number}})

But that doesn't help when there are multiple values in other columns than Work %

Thanks in advance for all help!

(I posted this in PowerBI desktop only to realize I was in the wrong forum. This is the repost in the correct forum.)

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJTc7Iy0xOzAGynfKTgKRrXnpmXmpqEZBpaGCgFKsTrWQEZAenFpVlJqeCtOSnpIAEEnNSi4G0ET41pQUF+UUlQJYxQhWylQh1IK3F+SBbTSFKjYFMjyAg4VaUmgJm6+Ym5iWmI1wWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Department = _t, Name = _t, Occupation = _t, #"Work %" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Department", type text}, {"Name", type text}, {"Occupation", type text}, {"Work %", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Work %", "Work Pct"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Employee ID"}, {{"AllRows", each _, type table [Employee ID=nullable number, Department=nullable text, Name=nullable text, Occupation=nullable text, #"Work %"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Total", each List.Sum([AllRows][Work Pct]), type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MaxRow", each Table.Max([AllRows], "Work Pct")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
    #"Expanded MaxRow" = Table.ExpandRecordColumn(#"Removed Columns", "MaxRow", {"Department", "Name", "Occupation", "Work Pct"}, {"Department", "Name", "Occupation", "Work Pct"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded MaxRow",{{"Department", type text}, {"Name", type text}, {"Occupation", type text}, {"Work Pct", Int64.Type}})
in
    #"Changed Type1"

Pat

[Anonymous]

This worked great! Thank you so much! When you find solutions like this, do you type in the code or do you play around with the UI until something works and then copy/paste the code here?