Count values of a column

[Anonymous]

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

[BA_Pete · Accepted solution]

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
    groupRows

Example output:

Pete