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

[thegusman]

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?

[artemus]

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])),

   ...

]

[thegusman]

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.

[artemus]

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

[thegusman]

Fruit	Vegetable	FruitCount	VegetableCount
Apple	Carrot	1	2
Orange	Carrot	3	2
Peach	Cucumber	2	2
Peach	Tomato	2	1
Orange	Onion	3	1
Orange	Cucumber	3	2

[artemus]

Ok, you do realize that your table isn't normalized though? E.g. In your example above, you have Orange 3 times, and for all 3 times FruitCount is 3. This can cause issues when you aggergate it on the visualization layer.

It is possible to do this, but it may not be what you want.

[thegusman]

I realize this table is very simple, but I have many columns that need to be counted.

I used a Text.Combine to group the columns that need to be counted together, since there are so many. 

[v-juanli-msft]

Hi @thegusman 

You could create calculated columns in Data model view,

fruit count = CALCULATE( COUNT('Table'[Fruit]),ALLEXCEPT('Table','Table'[Fruit]))

vege count = CALCULATE( COUNT('Table'[Vegetable]),ALLEXCEPT('Table','Table'[Vegetable]))

or create measures which can change with slicers

fruit count m = CALCULATE( COUNT('Table'[Fruit]),FILTER(ALLSELECTED('Table'),'Table'[Fruit]=MAX('Table'[Fruit])))

vege count m = CALCULATE( COUNT('Table'[Vegetable]),FILTER(ALLSELECTED('Table'),'Table'[Vegetable]=MAX('Table'[Vegetable])))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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