How to keep the earliest upload time per day (multiple rows per day)

[alya1]

Hello, 

I have a very long table with data from past 5 years (almost) everyday on our list of customers. 

Uploaded	Customer ID
1/1/2000 9:00	111
1/1/2000 9:00	222
1/1/2000 9:00	333
1/1/2000 10:03	111
1/1/2000 10:03	222
1/1/2000 10:03	333
1/1/2000 12:24	111
1/1/2000 12:24	222
1/1/2000 12:24	333
1/2/2000 8:00	222
1/2/2000 8:00	333
1/2/2000 8:00	444

etc.

How can I just keep the earliest upload time for each day (only 3 rows of 1/1/2000 9:00 and 3 rows of 1/2/2000 8:00 in the example table) and delete the other duplicate columns?

Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Uploaded", type datetime}, {"Customer ID", Int64.Type}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Uploaded]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted Date", {"Date"}, {{"Count", each Table.AddRankColumn(_,"Index","Uploaded")}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Uploaded", "Customer ID", "Index"}, {"Uploaded", "Customer ID", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Count", each ([Index] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

Hope this helps.

[alya1]

Thank you Ashish! Would you mind sharing your .pbix file please? I am running into a few errors 

[Ashish_Mathur]

You are welcome.  PBI file attached.

[ryan_mayu]

@alya1 

here is a workaround for you

[alya1]

Hi Ryan, thank you for your response! This works for this sample table but my actual data is from past 5 years with uploads (almost) everyday. It would be time consuming to create a date table with each date and first uploaded time. May I ask for any other ideas?