Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |