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

Learner86 , You can get in a table.

 

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

  • 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"