Forum Discussion
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 columns created, and rename them when I'm done? Is this best practice for distinct column value counts in query editor?
8 Replies
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])),
...
]
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 DistinctCountKind regards,
JB
Thanks Artemus,
I may have mispoke on what I was trying to accomplish.
Column 1 contains many different values. I want to see the frequecy of every value within that column in another column. I could do that with a group by Count, and add All Rows, but my problem is that there are many columns in that table that I also need the value's frequency.
I am dreading the thought of doing a group by 10+ times, and then cleaning. Especially since my data is 300k+ rows.
What is the format of the table you want to have at the end? E.g.
ColumnName Term Count Column1 Value1 4 Column1 Value2 6 Column2 Value1 1
