Forum Discussion
Resolver IIPower Query filter to only one row per day per user
Hello,
I am developing an attendance report with access control system data.
I am doing calculations of the shift of employees by the time they clock-in.
But sometimes an employee clocks-in twice for a day, and that calculates him two shifts for that day.
I would like to filter the second, third or other clock-ins with power query and have only 1 record per day per employee.
Does someone know some M Code formulas which could I use to achieve that?
No need to merge: you can select multiple columns before chosing "Remove Duplicates".
Your #"Table Buffer" step just takes the result from the previous step without buffering.
(The step name is just a name and performs no action).
You didn't mention anything about sorting.
This is my code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Sorted Rows" = Table.Sort(Source,{{"Time", Order.Ascending}}), #"Changed type of Event Date to date" = Table.TransformColumnTypes(#"Sorted Rows",{{"Event Date", type date}, {"Time", type time}, {"Card Number", type text}}), Buffered = Table.Buffer(#"Changed type of Event Date to date"), #"Removed Duplicates" = Table.Distinct(Buffered, {"Event Date", "Card Number"}) in #"Removed Duplicates"
6 Replies
- MarcelBeug
Community Champion
You can select the Date and Employee columns and choose Remove Rows - Remove Duplicates.
This will not necessatily keep the first occurrence. If that would be required, then you need to buffer your table after sorting (if not already sorted), using Table.Buffer(<Name of the preceding step>) or just wrap the Table.Buffer around the code of the previous step.
- Birdjo
Yes, I want to keep the first occurrence. Let's say it's min. time.
I don't know what exactly Table.Buffer, however I tried it and and applied Remove Duplicates aftert it.
It removed the earliest entry, leaving the latest.- MarcelBeug
That is very strange. Would you mind sharing the relevant part of your code?
If there is no explicit sort in your code: how is the data sorted?
