Forum Discussion

jwhitlow's avatar
jwhitlow
Regular Visitor
5 years ago
Solved

Logins Per Day

    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

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

4 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

    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's avatar
      jwhitlow
      Regular Visitor
      5 years ago

      Thanks for the quick reply! ğŸ˜€ Can you please provide a little more detail?

       

      In your example, what is 'PriorStepName'?

      • Jakinta's avatar
        Jakinta
        Solution Sage
        5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.