Count occurences in Power Query M

[Anonymous]

Hi everyone,

I have found an anomaly in my table which normally shouldn't be there and I want to filter the rows out in Power Query. 

Columns of interest: Project Number and DATE. I have projects that have multiple entries in a date which in this case is not ok and I want to filter the rows out but I cannot use remove duplicates as I have other conditions I need to apply, so in order to do this i have created a concatenated column like "PROJECT&DATE" so I want to count the frequency of each value in the column. 

In DAX this would be easy with COUNTROWS() and the right FILTER() but I have no idea how to do this in M.

So how can i count the frequency of each value in the column using M?

Thank you.

[dax · Accepted solution]

Hi florindaniel96,

You could try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWBsIzgLGM4ywTMSgayTOEsMzjLHMxKBbIswKwUIMsSzjI0QDAhthSDmEYIJtCeWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"Count", each Table.RowCount(_), type number}, {"all", each _, type table [name=text, id=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"id"}, {"all.id"}),
    #"Added Custom" = Table.AddColumn(#"Expanded all", "Custom", each [Count]/Table.RowCount(#"Expanded all"))
in
    #"Added Custom"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.