cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Birdjo Member
Member

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

clockins.png
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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Power Query filter to only one row per day per user

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"

 

Specializing in Power Query Formula Language (M)
6 REPLIES 6
MarcelBeug Super Contributor
Super Contributor

Re: Power Query filter to only one row per day per user

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.

Specializing in Power Query Formula Language (M)
Birdjo Member
Member

Re: Power Query filter to only one row per day per user

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 Super Contributor
Super Contributor

Re: Power Query filter to only one row per day per user

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?

Specializing in Power Query Formula Language (M)
Birdjo Member
Member

Re: Power Query filter to only one row per day per user

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 Super Contributor
Super Contributor

Re: Power Query filter to only one row per day per user

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"

 

Specializing in Power Query Formula Language (M)
Birdjo Member
Member

Re: Power Query filter to only one row per day per user

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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 44 members 1,048 guests
Please welcome our newest community members: