Forum Discussion
Use Count as a filter to exclude from database/visualization
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]))
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]),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.
Count is not a column otherwise I would have used it as a filter. Thanks danextian
Please see my other reply.