Forum Discussion

HarveyM's avatar
HarveyM
Icon for Helper I rankHelper I
5 years ago
Solved

Unique Login Tracking

Hello, I am having issues implementing a unique login tracker for a data set I have. Firsly, a unique login is classed as an individual users first login within a 24 hour time period. However the ti...
Need Help
Tutorial Requests
  • Jakinta's avatar
    Jakinta
    5 years ago

    If you want it in M...

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbLQN9Q3MlQwNLUyMFCK1UEVNMMmaA4TdEIStMCmEqugJaqgEVjQBJugKapFRti0G2NzkglY0ACLoBFEMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Login = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    ChangedType = Table.TransformColumnTypes(#"Added Index",{{"Login", type datetime}}),
    #"Grouped Rows" = Table.Group(ChangedType, {"User"}, {{"Gr", each  Table.AddColumn(_, "Unique", each let 
l=List.Transform(ChangedType[Login], Number.From),
x=List.Accumulate(l, {l{0}}, (s,c)=> if c< List.Last(s)+1 then s else s&{c})
in 
List.Contains(x,Number.From([Login]))) , type table}}),
    #"Expanded Gr" = Table.ExpandTableColumn(#"Grouped Rows", "Gr", {"Index", "Login", "Unique"}, {"Index", "Login", "Unique"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Gr",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"
HarveyM's avatar
HarveyM
Icon for Helper I rankHelper I
5 years ago

Hi AlB 

 

Yes it would be good in a custom column.

Below is some exapmle data with the expected result.

So from the last login, every login until 07/07/2021 19.09 will not be unique as they fall into the last 24hr window.

In regards to your question, the frist login for each 24 hour period would be uniqe, the subsquent 23 for the rest of the day are not unique. In total you would have 3 unique logins.

Jakinta's avatar
Jakinta
Icon for Solution Sage rankSolution Sage
5 years ago

If you want it in M...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbLQN9Q3MlQwNLUyMFCK1UEVNMMmaA4TdEIStMCmEqugJaqgEVjQBJugKapFRti0G2NzkglY0ACLoBFEMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Login = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    ChangedType = Table.TransformColumnTypes(#"Added Index",{{"Login", type datetime}}),
    #"Grouped Rows" = Table.Group(ChangedType, {"User"}, {{"Gr", each  Table.AddColumn(_, "Unique", each let 
l=List.Transform(ChangedType[Login], Number.From),
x=List.Accumulate(l, {l{0}}, (s,c)=> if c< List.Last(s)+1 then s else s&{c})
in 
List.Contains(x,Number.From([Login]))) , type table}}),
    #"Expanded Gr" = Table.ExpandTableColumn(#"Grouped Rows", "Gr", {"Index", "Login", "Unique"}, {"Index", "Login", "Unique"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Gr",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"