Forum Discussion
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 ...
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.
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.
