Forum Discussion

thegusman's avatar
thegusman
Helper I
6 years ago

Distinct Count in Query Editor... Is Group by the only solution

I have multiple  columns that I need to do a distinct count within query editor.  It seems that Group By is the only option...  Do I need to preform a group by for every column, delete the extra colu...
Need Help
artemus's avatar
artemus
Microsoft Employee
6 years ago

If you want a distinct count of various columns you could:

 

[

   Column1Count = List.Count(List.Distinct(Table[Column1])),

   Column2Count = List.Count(List.Distinct(Table[Column2])),

   Column3Count = List.Count(List.Distinct(Table[Column3])),

   ...

]

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi thegusman ,

 

same as above in the artemus post, but agnostic to column names:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc3LDQAhCEXRXli7APxSi6H/NsanYjILSe4J6pykLEaJhNcY5AmiCJE1LAQhehYv7arHL2VUw8hB5W2VIBTvcwE3zp8tqKPsR+O9VMn9Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Sales = _t, Costs = _t]),
    DistinctCount = List.Accumulate(Table.ColumnNames(Source), [], (s,a)=> Record.AddField(s, a, List.Count(List.Distinct(Table.Column(Source, a)))))
in
    DistinctCount

Kind regards,

JB