Power Query filter to only one row per day per user

[Birdjo]

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?

[MarcelBeug]

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?

[Birdjo]

Here is what I did:
1. Merged date and card number so there should not be duplicates in this value:

 #"Inserted Merged Column" = Table.AddColumn(#"Changed type of Event Date to date", "Date time card", each Text.Combine({Text.From([Event Date], "bg-BG"), Text.From([Card Number], "bg-BG")}, " "), type text),

2. Then added Table.Buffer

#"Table Buffer" = (#"Inserted Merged Column"),

3. Then I remove the duplicates

#"Removed Duplicates" = Table.Distinct(#"Table Buffer", {"Date time card"})

And what happened is that not the earliest but the latest duplicate record was kept.

[MarcelBeug · Accepted solution]

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"

[Birdjo]

Well, I can't understand how exactly Table.Buffer function works but that did the job, so thank you very much!