Count recurrence of values from a column in Power query

[kabra_ashish]

How can I get a count of every value in a column in Power Query? More like Countif in Excel. This is certainly doable using DAX but I am interested in doing this in Power Query.

I am looking for the below "Count" field as an output using the Fruit field. 

Fruit          Count

Apple            3

Orange         1

Banana         2 

Apple           3

Apple           3

Banana         2

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to achieve this in Power Query Editor is to write M code something like below.

Please check the below and the attached pbix file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElVitWJVvIvSsxLhzCdEvOAEMxEyCNYMOlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Fruit"}, Source, {"Fruit"}, "Source", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Rowcount", each Table.RowCount ( [Source] ), Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Fruit", "Rowcount"})
in
    #"Removed Other Columns"

[amitchandak]

@kabra_ashish , Duplicate this table or create a new table using the fruit column.

Then group by and get count.

Merge the second table with the first one

https://docs.microsoft.com/en-us/power-query/group-by
Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query