Group-By & Aggregate

[EithneDangan]

Hi Everyone One,

I am new to power query and would gratefully appreciate some assistance on a grouping question.  I have created a query to generate a payroll that can contain Duplicate Employee ID's see 258 Below 

I would like one record per Employee id, listing the PPS No, Employee name and one of the clients with totals created for numeric columns.  This employee has 23 records as he fored for 2 clients.  It does not matter what client name appears in the aggregated result:

Using Advanced Group By - Emp ID and then Grouping for the numeric fields works perfect, but how do I display the text fields also.  Hope I am making sense.

Any help gladly appreciated 

Eithne

[Jimmy801 · Accepted solution]

Hello @EithneDangan 

group your first 3 columsn (these columns where the EMPID has unique values) and apply 3 function to it. 2 that sum hours and rate and one that combine your clients name into one cells using Text.Combine. here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjK1UNJRMgRiRyB2zslMzStRADGNzXVMQTIGOhZKsTo4FDqhKDQEKzQ2APKMgNgJoc4ZWR1YlQlIlTFIBUKVC4pp5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EMPID = _t, #"PPS No" = _t, #"Employee Name" = _t, #"Client Name" = _t, Hours = _t, #"Standard rate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPID", Int64.Type}, {"PPS No", Int64.Type}, {"Employee Name", type text}, {"Client Name", type text}, {"Hours", type number}, {"Standard rate", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EMPID", "PPS No", "Employee Name"}, {{"SumHours", each List.Sum([Hours]), type number}, {"SumRate", each List.Sum([Standard rate]), type number}, {"CombineClient", each Text.Combine(_[Client Name], " - ")}})
in
    #"Grouped Rows"

it transform this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy