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 syntax in Power Query to compute distinct count values in new custom column?

 

Thank you very much for your proposed solutions.

4 Replies

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

         

         

         

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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