Logins Per Day

[jwhitlow]

    I am hoping someone can guide me with how to take a table with individual logins data and create a summary table. This is an example of what my source table look like:

Name	Timestamp
Betty	2021-07-15 23:00
Betty	2021-07-15 23:00
Betty	2021-07-15 21:28
Hellen	2021-07-26 10:39
Hellen	2021-07-26 09:26
Milo	2021-07-26 09:40
Milo	2021-07-15 05:40
Patrick	2021-07-16 06:31
Patrick	2021-07-15 08:00
Patrick	2021-07-14 13:38
Patrick	2021-07-14 10:33
Patrick	2021-07-14 07:44
Robert	2021-07-26 04:14
Robert	2021-07-15 07:44
Robert	2021-07-15 04:15
Susan	2021-07-23 00:59
Susan	2021-07-22 01:01
Susan	2021-07-12 01:01
Will	2021-07-13 06:46

  Below is the result I am trying to accomplish. I am able to do this in Excel using VBA. I am trying to replicate this in Power BI without using VBA.

Name	Logins	Days	Average
Betty	3	1	3.00
Hellen	2	1	2.00
Milo	2	2	1.00
Patrick	5	3	1.67
Robert	3	2	1.50
Susan	3	3	1.00
Will	1	1	1.00

[V-pazhen-msft · Accepted solution]

@jwhitlow 

You need to also remove time value to get distinctcount for days. Copy and paste the code to a blank query, and check the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndE9C8IwEAbgv3J0buEul37d6OQiiA4OpUOVDsVgocbBf2+ChVJtI7hkeR9e7i5VFcGmtfYZxRGAQkUJ5gmloFgQIarj/wGJKkawbY1pbzOhMiAULoMCS3HvW+w60y/kGtdyNwOmU75v7NBdrnPiKjJhChHXUkyrLhINxMLFD+K25TDBXLQeyaE/t4P9WlgLBYQfNtzhhetIR3F83JuPqzMgSlquAwVIgrQKaA5OnTHznP3Rtf/X+gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"  Timestamp" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"  Timestamp", type datetime}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"  Timestamp", type text}}, "zh-CN"), "  Timestamp", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"  Timestamp.1", "  Timestamp.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"  Timestamp.2"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Name"}, {{"logins", each Table.RowCount(_), Int64.Type}, {"days", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Average", each [logins]/[days])
in
    #"Added Custom"

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Jakinta]

You can group them like this

= Table.Group(PriorStepName, {"Name"}, {{"Logins", each Table.RowCount(_), Int64.Type} , {"Days", each Table.RowCount(Table.Distinct(_)), Int64.Type}})

then divide Logins/Days to get the Average.

[jwhitlow]

Thanks for the quick reply! 😀 Can you please provide a little more detail?

In your example, what is 'PriorStepName'?

[Jakinta]

'PriorStepName' is the name of the last step in your existing query.