DISTINCTCOUNT function with filter <5

[Anonymous]

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 the data is received from the server). The datetime column is made up of a series of euipment events, all with the same time stamp, and so on for the next 15min period etc (see screen dump)

In the same measure I want to, exclude any events within that datetime column timestamp with <5 events, and exclude these from the equipment runtime. I have racked my brain for an expression for this, I have attached a screen dump and an example i would want to exclude. Any help would be very much appreciated and thanks in advance.

[mahoneypat · Accepted solution]

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]

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]

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.