Forum Discussion

KatkaS's avatar
KatkaS
Post Patron
5 years ago
Solved

Keep first value

Hello,   I have a simple excel sheets with names and dates when they logged in an application. I need to keep (count) only their first log-in, delete the rest (or keep, but just count the first lo...
  • fhill's avatar
    fhill
    5 years ago

     

    In Query Editor, try the Group By Feature -> Advanced to Select the MIN Login (by Email) and COUNT of Rows.

     

    After that, you can take the GROUPED DATA into a simple chart.

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Logged in", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Logged in", Order.Ascending}}),
    Partition = Table.Group(#"Sorted Rows", {"Name"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Logged in", "Index"}, {"Logged in", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Action", each if [Index]=1 then "Keep" else "Delete"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

If you do not want to Load the "Delete" rows to your Data Model, then you can simply filter out those values.

Hope this helps.