count the number of times an item appears in a column

[monojchakrab]

Hiya good people,

I am trying to add a column in my power query table which, for each value of the customer name column, should return the number of times that particular value appears in that column :

There should be an easy fix for this with something like List.Count, but somehow I am not able to get around to it.

Any help much appreciated

regds.,

[m_dekorte · Accepted solution]

Hi @monojchakrab,

You might need to convert names to either upper or lower case before a count.

2 ways for you to explore, option 1 creating an aggregate table.

First copy (do not reference) the query, keep only the [Customer Name] column

Go to Group By and add a count

Merge or Join the result back to your query

Option 2, performing a calculation in your table

Add a step (above the Source) to create a list with all Customer Names, that looks like this

custList = List.Buffer( LastStepName[Customer Name] )

Now you can use the Add Custom Column to count occurances, in the dialog box enter this code:

List.Count( List.Select( custList, each _ = [Customer Name] ))

Ps. If this helps solve your query please mark this post as Solution, thanks!

[AlienSx · Accepted solution]

Hi @monojchakrab . This code is case insensetive. Otherwise simply use Table.Group to group by name and calc rows count.

let
    Source = your_source_table,
    fg = Table.FuzzyGroup(Source, "Customer Name", {{"all", each _}, {"count", each Table.RowCount(_), Int64.Type}}, [IgnoreCase = true]),
    expand = Table.ExpandTableColumn(fg, "all", {"Date"})
in
    expand

If you choose to ignore case when grouping but retain original names in resulting table (keep "Bourbon Street" and "BOURBON STREET") then expand all columns of tables in [all], then remove "Customer Name" column and rename expanded name column (Customer Name.1) to your liking. 

[m_dekorte · Accepted solution]

Hi @monojchakrab,

You might need to convert names to either upper or lower case before a count.

2 ways for you to explore, option 1 creating an aggregate table.

First copy (do not reference) the query, keep only the [Customer Name] column

Go to Group By and add a count

Merge or Join the result back to your query

Option 2, performing a calculation in your table

Add a step (above the Source) to create a list with all Customer Names, that looks like this

custList = List.Buffer( LastStepName[Customer Name] )

Now you can use the Add Custom Column to count occurances, in the dialog box enter this code:

List.Count( List.Select( custList, each _ = [Customer Name] ))

Ps. If this helps solve your query please mark this post as Solution, thanks!