Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
  • mahoneypat's avatar
    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