Distinct count in custom column

[Learner86]

Hi everybody,

I have a column with the name of customers, like this:

Costumer

A

A

B

C

C

D

E

F

F

Some names appear once and other names appear multiple times. How can I write a syntax in Power Query to compute distinct count values in new custom column?

Thank you very much for your proposed solutions.

[mahoneypat]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGYl0AZOuYNINRsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let thiscustomer = [Customer] in Table.RowCount(Table.SelectRows(Source, each [Customer] = thiscustomer)), Int64.Type)
in
    #"Added Custom"

Pat

[amitchandak]

@Learner86 , You can get in a table.

You can remove duplicate values. Or create a copy of the table and remove duplicate values.

[Learner86]

@amitchandak: Thank you for the solution. I want to keep the duplicate values. I want to have a new column like this:

Costumer Newcolumn

A                2

A                2

B                1

C                2

C                2

D                1

E                 1

F                 2

F                 2

[VahidDM]

Hi @Learner86 

In Power Query add t

ry this code:

1- in Applied steps click on "Insert Step After"

2- copy this code and past in the new step:

= Table.RemoveColumns(Table.ExpandTableColumn(Table.Group(Source, {"Costumer"}, {{"All Row", each _, type table [Costumer=nullable text]}, {"Count", each Table.RowCount(_), Int64.Type}}), "All Row", {"Costumer"},{"All Row"}),{"All Row"})

Output:

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

Appreciate your Kudos  !!

**Whole M codes are as below (For your reference):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGYl0AZOuYNINRsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Costumer = _t]),
    #"Removed Columns" = Table.RemoveColumns(Table.ExpandTableColumn(Table.Group(Source, {"Costumer"}, {{"All Row", each _, type table [Costumer=nullable text]}, {"Count", each Table.RowCount(_), Int64.Type}}), "All Row", {"Costumer"},{"All Row"}),{"All Row"})
in
    #"Removed Columns"