cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

count distinct on column in power query

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?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: count distinct on column in power query

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.

 

Group By.png

Specializing in Power Query Formula Language (M)
6 REPLIES 6
Super User
Super User

Re: count distinct on column in power query

Don't adjust the code but start over. First select only the "APARTMENT" column and then choose Group By.

Specializing in Power Query Formula Language (M)
Super User
Super User

Re: count distinct on column in power query

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.


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Super User
Super User

Re: count distinct on column in power query

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.

 

Group By.png

Specializing in Power Query Formula Language (M)
Super User
Super User

Re: count distinct on column in power query

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 !


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
affan Established Member
Established Member

Re: count distinct on column in power query

@MarcelBeug Thanks for the fantastic tip. You've made my day.

Highlighted
kbarber Frequent Visitor
Frequent Visitor

Re: count distinct on column in power query

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):

 

simpletable.png

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:

 

simpletable2.png

 

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