Forum Discussion

Learner86's avatar
Learner86
Icon for Helper I rankHelper I
5 years ago

Distinct count in custom column

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 sy...
Learner86's avatar
Learner86
Icon for Helper I rankHelper I
5 years ago

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's avatar
VahidDM
Icon for Super User rankSuper User
5 years ago

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"