Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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])),
...
]
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 |
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 |
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.
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |