Forum Discussion
Use Count as a filter to exclude from database/visualization
Hello users,
I have a huge database and I need to exclude all devices with a count of 1.
Example: There are devices with a count of 1 and more than 1. I would like to exclude Device "1003","4001","5100" from my database as a filter. Please help.
| Device | Count |
| 1000 | 3 |
| 1001 | 2 |
| 1002 | 10 |
| 1003 | 1 |
| 4001 | 1 |
| 4002 | 5 |
| 5100 | 1 |
danextian I used SUMMARIZE function to get count of Device IDs. Used it as a filter in my dashboard and the solution has worked.
SN_Count = SUMMARIZE('fact CEC_SR_RAW','Table'[Machine_Serial_Number],"DSN",COUNT('fTable'[Machine_Serial_Number]))
7 Replies
hi prasadhebbar315 ,
Is the count column already in the database? If so, you can just filter them out in Power Query
If the count column doesnt exist in the data source, you can use Table.Group to create such and then filter to exclude anything > 1. Try this sample code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJJLctMTlVwVIrViVZyQgg4gQWcEQLOYAEXhIALWADDDDeEgBtYwB0h4E6kLa4IAVewgBdCwEspNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Device = _t, #"Device Name" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Device", type text}, {"Device Name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Device"}, {{"All Columns", each _, type table [Device=nullable text, Device Name=nullable text]}, {"Count", each Table.RowCount(_), Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] <= 1), #"Expanded All Columns" = Table.ExpandTableColumn(#"Filtered Rows", "All Columns", {"Device Name"}, {"Device Name"}) in #"Expanded All Columns"The below text is throwing error. How to eliminate them?
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJJLctMTlVwVIrViVZyQgg4gQWcEQLOYAEXhIALWADDDDeEgBtYwB0h4E6kLa4IAVewgBdCwEspNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Device = _t, #"Device Name" = _t]),
Count is not a column otherwise I would have used it as a filter. Thanks danextian
Please see my other reply.
