Forum Discussion
Solved
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","...
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]))
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]),
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.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]))