Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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