Use Count as a filter to exclude from database/visualization

[prasadhebbar315]

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]

hi @prasadhebbar315 ,

Is the count column already in the database? If so, you can just filter them out in Power Query

[prasadhebbar315]

Count is not a column otherwise I would have used it as a filter. Thanks @danextian 

[danextian]

Please see my other reply.

[danextian]

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"

[prasadhebbar315]

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]),

[danextian]

What error are you getting. Tested just that line myself and it didnt' return an error

Also the whole script I gave you should be pasted in a blank query using the Advanced Editor. Be sure to delete everything else before pasting it.

[prasadhebbar315 · Accepted solution]

@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]))