Forum Discussion
Anonymous
4 years agoNot applicable
DISTINCTCOUNT function with filter <5
Hi all, I need assitance with a DISTINCTCOUNT function in a datetime column. So I DISTINCTCOUNT the datetime column and add the 15min increments up and divide by 4 for hourly runtime, (this is how th...
- 4 years ago
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. You just need a Group By step with two aggregations - one for the count the other to keep All Rows. You then filter where the count is >= 5 and then re-expand the AllRows.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcjJCQAgDATAXvYtYmK8agn234aQj7jOc9whSDCzNXMR7ORQjsphHI2j39CIcaNGTI7FIeUb+Uaf2Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Timestamp = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Timestamp", type number}}), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"ID", Int64.Type}, {"Timestamp", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Timestamp"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"AllRows", each _, type table [ID=nullable number, Timestamp=nullable datetime]}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] >= 5)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"AllRows"}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Other Columns", "AllRows", {"ID", "Timestamp"}, {"ID", "Timestamp"}) in #"Expanded AllRows"Pat
Anonymous
4 years agoNot applicable
Thanks Pat, thats looks a bit complex for me, would you be able to asist by inserting my data, columns? Appreciate it if you dont, thought id ask. Thanks Daniel
AlexisOlson
4 years agoSuper User
If you make your data available in a more friendly format, I bet someone would be much more likely to use it. It's not reasonable to expect someone to re-type that much data from a screenshot.