Forum Discussion
Advocate IUse 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]))
Super Userhi prasadhebbar315 ,
Is the count column already in the database? If so, you can just filter them out in Power Query
Super UserIf 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
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]))