Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.