Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
i am trying to group by in power query but i only want it to count distinct on one column.
this is the current m code
#"Grouped Rows" = Table.Group(Source, {"PLOT", "BLOCK", "UNIT TYPE", "APARTMENT"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
how would i adjust it to do a distinct count on apartment?
Proud to be a Super User!
Solved! Go to Solution.
In Group By - Advanced - you can add an aggregation and choose All Rows to get all data.
That will give you a column with nested tables you can expand.
list.count on list.distinct should do the trick
{{"number_of_flows", each List.Count(List.Distinct([APARTMENT])), Int64.Type}
if you want to exclude blanks from results
{{"number_of_flows", each List.NonNullCount(List.Distinct([APARTMENT])), Int64.Type}
Thanks for saving my time, I've applied this to my report and it work!!
There is no solution using visual
= Table.Group(#"Expanded chat messages", {"keyword_category"}, {{"messages", each List.Count(List.Distinct([id])), Int64.Type}, {"users", each List.Count(List.Distinct([user_id])), Int64.Type}})
Don't adjust the code but start over. First select only the "APARTMENT" column and then choose Group By.
ok but will that output all the other columns too? i need the count distinct of the apartments but by all those columns... sorry if i am not getting it.
Proud to be a Super User!
In Group By - Advanced - you can add an aggregation and choose All Rows to get all data.
That will give you a column with nested tables you can expand.
Wow, this has just simplified so many of my queries! Thanks so much!
Hello,
I believe i have the same, or very similar, requirement and am not getting to the solution, though I think I am very close. To use a simplified example, consider this table (sorted by position):
I would like to use PowerQuery to add a column that shows the DISTINCT COUNT OF SUPERVISORS PER POSITION.
So, the end result would be this:
I can't seem to get @MarcelBeug's solution to work for me.
Here's the example table: Example Data
Any help would be greatly appreciated,
Thank you,
- Kurt
Did you get the solution to your problem?
I never got it to work as intended this solution, i just ended up grouping by again, but i have to say ive been using this trick alot for other group by's and its great so thank you @MarcelBeug !
Proud to be a Super User!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
27 |
User | Count |
---|---|
92 | |
50 | |
44 | |
40 | |
35 |