Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
hi @prasadhebbar315 ,
Is the count column already in the database? If so, you can just filter them out in Power Query
Proud to be a Super User!
Count is not a column otherwise I would have used it as a filter. Thanks @danextian
Please see my other reply.
Proud to be a Super User!
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"
Proud to be a Super User!
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.
Proud to be a Super User!
@danextian I used SUMMARIZE function to get count of Device IDs. Used it as a filter in my dashboard and the solution has worked.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
106 | |
86 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |