Group-By & Aggregate

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