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 ...
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 |