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