Forum Discussion
Obtaining unique values based on a given condition
Hello Everyone,
I currently have a query that contains data from hours reported towards different companies in 2022-2023 organized on a daily date format. Therefore for each day, I have a split of hours and sometimes one employee can work for several different companies during the same day. What I need is to obtain a new table where I only keep the company where the employee reported the most of its time to as shown in the tables.
Current table
| Employee ID | Company | Working Hours | Date |
123 | BananasCompany | 7 | 2/14/2022 |
| 123 | ComputersCompany | 1 | 2/14/2022 |
| 123 | Computers Company | 6 | 2/15/2022 |
| 123 | Bananas Company | 1 | 2/15/2022 |
| 123 | water Company | 1 | 2/15/2022 |
Expected table
| Employee ID | Company | Working Hours | Date |
123 | BananasCompany | 7 | 2/14/2022 |
| 123 | Computers Company | 6 | 2/15/2022 |
Notice It happens to multiple employees at once in the real data base therefore I will hold several employee id associated to each date but each employee ID can be found on each date only once, by the condition of keeping the value that has the most of the time reported for that day.
Appreciate your help
8 Replies
hi tom1tas ,
create a blank query and in the advanced editor , copy paste the following code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRckrMA8Ji5/zcgsS8SqCAORAb6Rua6BsZGBkpxerAFIJUlJakFiEpNSSkVAGh1gyi1hRdLdR+BQxTMVSWJwKNxK0uFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Company = _t, #"Working Hours" = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Company", type text}, {"Working Hours", Int64.Type}, {"Date", type date}}), #"Grouped Rows by Employee and Date" = Table.Group(#"Changed Type", {"Employee ID", "Date"}, {{"Details", each _, type table [Employee ID=nullable number, Company=nullable text, Working Hours=nullable number, Date=nullable date]}}), #"Added Index Column in the Details Column" = Table.AddColumn(#"Grouped Rows by Employee and Date", "Custom", each Table.AddIndexColumn([Details],"Index")), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Index Column in the Details Column", "Custom", {"Company", "Index"}, {"Company", "Index"}), #"Take Value with Most hours worked" = Table.SelectRows(#"Expanded Custom", each ([Index] = 0)), #"Removed Columns" = Table.RemoveColumns(#"Take Value with Most hours worked",{"Details", "Index"}) in #"Removed Columns"Anonymous tom1tas ,
So first two steps don't really matter ( source and change type)
After that:
1. Grouped rows by employee id and date aggregating "details" as all the rows which will give a table with all rows for that employee id and date.
2. Added an index column called "Custom" in this "Details" table
3. Expand Company, hours worked and index column
4. Filter index for 0. ( First row contains the record with MAX working hours) .
Hope this helps.
Can you explain it as query steps? I used dummy data therefore it does not work for me to get m code solutions
NewStep=Table.FromRecords(Table.Group(PreviousStepName,"Employee",{"n",each Table.Max(_,"Working Hours")})[n])
I need it to keep all dates historically for each employee id, the only condition is that I need to keep one company per each date based on the maximum number of hours reported.
This m code script keeps only maximum from latest date available, is there a way to keep all the dates available and in case the employee worked for two companies the same day the same amount of time, to condition it to choose whichever company that is not "milk company"Employee id company hours date 123 Milk Company 3 1/1/2023 123 Cars Company 3 1/1/2023 Expected table
Employee id company hours date 123 Cars Company 3 1/1/2023 Scenario where hours amount is different from each company
Existing table
Employee id company hours date 123 Goats Company 4 1/1/2023 123 Cars Company 3 1/1/2023 Expected table
Employee id company hours date 123 Goats Company 4 1/1/2023
