Keep first value

[KatkaS]

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 login).

Result will be a stacked column chart: Number of log-ins and month+year.

Could anyone please help?

Thank you!!

Name	Logged in	Result
Anna	26/8/2020	keep
Anna	29/8/2020	delete
Albert	01/08/2020	keep
Anna	31/8/2020	delete
Cecile	01/01/2020	keep

[Ashish_Mathur]

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.

 

[fhill · Accepted solution]

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.

[KatkaS]

@fhill THANK YOU SO MUCH, it worked!!!

[Greg_Deckler]

@KatkaS - Seems like:

Measure =
  VAR __Month = MAX('Calendar'[Month])
  VAR __Year = MAX('Calendar'[Year])
  VAR __Table = SUMMARIZE(ALL('Table'),[Name],"First",MIN([Logged in]))
RETURN
  COUNTROWS(FILTER(__Table,YEAR([First])=__Year && MONTH([First])=__Month))

[KatkaS]

Thank you, Greg, but I'm afraid this is too advanced for me. You mentioned MAX('Calendar'[Month])

 - do I need to create new table with periods and create relationship between the User login table..? 

[amitchandak]

@KatkaS , not very clear. Do we need to calculate first log in (Logic)

calculate(countrows(table),filter(table, table[Result] in{"delete","First log in"}))

[KatkaS]

Thank you, Amit. I tried your measure, but I don't understand what you mean by in{"delete","First log in"})) - do I need to add new column "First log in"..?

And yes, we need to count how many new logins were every month - month are on X axis of a stacked column chart..