topic Re: Distinct count in custom column in DAX Commands and Tips

Distinct count in custom column

Learner86 — Wed, 01 Sep 2021 09:49:46 GMT

Hi everybody,

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

Costumer

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.

Re: Distinct count in custom column

amitchandak — Wed, 01 Sep 2021 09:55:19 GMT

Learner86 , You can get in a table.

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

Re: Distinct count in custom column

Learner86 — Wed, 01 Sep 2021 11:00:14 GMT

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

B 1

C 2

D 1

E 1

F 2

Re: Distinct count in custom column

mahoneypat — Wed, 01 Sep 2021 12:02:51 GMT

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

Re: Distinct count in custom column

VahidDM — Wed, 01 Sep 2021 13:02:13 GMT

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"