Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
74 | |
74 | |
56 | |
37 | |
33 |
User | Count |
---|---|
71 | |
65 | |
58 | |
50 | |
47 |