Forum Discussion
Count values of a column
i have a sample data:Please help i really need this
| Year | Month | name0 | Count of name0 |
| 2021 | May | abc | 1 |
| 2021 | May | ghi | 2 |
| 2021 | May | sodoko | 1 |
| 2021 | May | AMAZON LTD | 1 |
| 2021 | May | MARKS AND SPENCER P.L.C. | 1 |
| 2021 | May | lakme | 1 |
| 2021 | JUNE | PANTALOON | 6 |
| 2021 | AUG | YOUTH | 1 |
THE OUT PUT I WANT ; it will eliminate those companies whose count is more than 1.P:S we cant include name in the final table
| Year | Month | Count of name0 |
| 2021 | May | 5 |
| 2021 | Aug | 1 |
Hi Anonymous ,
In Power Query, select your [Count of name] column, select the dropdown at the top of the column, then select 'Number Filters' > 'Equals...' > Enter 1.
Thn multi-select (Ctrl+Click) your [Year] and [Month] columns, go to the Home tab > Group By, and leave the aggregation column as Count Rows.
Power Query example code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlTSUfJNrASSiUnJQNJQKVYHTSI9IxNIGmFKFOen5GfnY9fk6OsY5e+n4BPigl3e1zHIO1jB0c9FITjA1c/ZNUghQM9Hz1kPu+qcxOzcVHQpr1A/VyAV4OgX4ujj7+8HZJshyzuGugPJSP/QEA+I1lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, name0 = _t, #"Count of name0" = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}, {"name0", type text}, {"Count of name0", Int64.Type}}), filterOne = Table.SelectRows(chgTypes, each [Count of name0] = 1), groupRows = Table.Group(filterOne, {"Year", "Month"}, {{"Count", each Table.RowCount(_), Int64.Type}}) in groupRowsExample output:
Pete
1 Reply
Hi Anonymous ,
In Power Query, select your [Count of name] column, select the dropdown at the top of the column, then select 'Number Filters' > 'Equals...' > Enter 1.
Thn multi-select (Ctrl+Click) your [Year] and [Month] columns, go to the Home tab > Group By, and leave the aggregation column as Count Rows.
Power Query example code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlTSUfJNrASSiUnJQNJQKVYHTSI9IxNIGmFKFOen5GfnY9fk6OsY5e+n4BPigl3e1zHIO1jB0c9FITjA1c/ZNUghQM9Hz1kPu+qcxOzcVHQpr1A/VyAV4OgX4ujj7+8HZJshyzuGugPJSP/QEA+I1lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, name0 = _t, #"Count of name0" = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}, {"name0", type text}, {"Count of name0", Int64.Type}}), filterOne = Table.SelectRows(chgTypes, each [Count of name0] = 1), groupRows = Table.Group(filterOne, {"Year", "Month"}, {{"Count", each Table.RowCount(_), Int64.Type}}) in groupRowsExample output:
Pete